"""
FIVEDASH(TM) -- General Purpose Accounting Software
Copyright (C) 2008  Obol Software Pty Ltd

This program is free software: you can redistribute it and/or modify
it. For terms and conditions, see fivedash/license/license.txt

"""

start_sql_map = {'create_init_db_func':
"""CREATE FUNCTION init_db() RETURNS INT AS $init_db$
BEGIN
SET DATESTYLE = 'ISO';
CREATE SCHEMA system;
CREATE SCHEMA ledger;

--app tables
CREATE TABLE system.sys_code (code_id  VARCHAR(20) NOT NULL, code_value VARCHAR(100) NOT NULL, code_desc VARCHAR(200), PRIMARY KEY (code_id, code_value));
CREATE TABLE system.row_update_log (table_name VARCHAR(100), where_cond VARCHAR(250), cols_updated VARCHAR(100)[], updated TIMESTAMP, PRIMARY KEY (table_name, where_cond, updated));

INSERT INTO system.sys_code VALUES ('contact_type', 'phone', 'phone');
INSERT INTO system.sys_code VALUES ('contact_type', 'fax', 'fax');
INSERT INTO system.sys_code VALUES ('contact_type', 'email', 'email');
INSERT INTO system.sys_code VALUES ('contact_type', 'mobile', 'mobile');
INSERT INTO system.sys_code VALUES ('contact_type', 'sms', 'sms');
INSERT INTO system.sys_code VALUES ('contact_type', 'abn', 'abn');
INSERT INTO system.sys_code VALUES ('contact_type', 'position', 'position');
INSERT INTO system.sys_code VALUES ('alt_location_type', 'home', NULL);
INSERT INTO system.sys_code VALUES ('alt_location_type', 'work', NULL);
INSERT INTO system.sys_code VALUES ('alt_location_type', 'postal_address', NULL);
INSERT INTO system.sys_code VALUES ('alt_location_type', 'courier_address', NULL);
INSERT INTO system.sys_code VALUES ('alt_location_type', 'other', NULL);
INSERT INTO system.sys_code VALUES ('contact_type', 'other', 'other');
INSERT INTO system.sys_code VALUES ('tax_page_md5','',NULL);
INSERT INTO system.sys_code VALUES ('default_tax', '', 'default tax for invoice');
INSERT INTO system.sys_code VALUES ('default_inv_terms', '30', 'default invoice terms');
INSERT INTO system.sys_code VALUES('billing_method','hard copy','hard copy');
INSERT INTO system.sys_code VALUES('billing_method','email','email');
--INSERT INTO system.sys_code VALUES('billing_method','fax','fax');		

-- a function required for one of the financial table definitions below
CREATE FUNCTION system.default_inv_terms() RETURNS SMALLINT AS $$
DECLARE
	v_terms SMALLINT;
BEGIN
	SELECT INTO v_terms CAST(code_value AS SMALLINT) FROM system.sys_code WHERE code_id = 'default_inv_terms';
	RETURN v_terms;
END;
$$ LANGUAGE plpgsql;

-- financial tables
CREATE TABLE system.journal_item_type (id VARCHAR(2) PRIMARY KEY);
CREATE TABLE system.account_type (id VARCHAR(20) PRIMARY KEY, sum VARCHAR(2) REFERENCES system.journal_item_type, ord INT);
CREATE TABLE ledger.general_chart_of_accounts (id VARCHAR(10) PRIMARY KEY, name VARCHAR(100) UNIQUE NOT NULL, account_type_id VARCHAR(20) REFERENCES system.account_type ON UPDATE CASCADE, subsidiary_shortname VARCHAR(100) UNIQUE, opening_balance NUMERIC, is_active BOOLEAN DEFAULT TRUE);
CREATE TABLE system.journal (id SERIAL PRIMARY KEY, date DATE, ledger_shortname VARCHAR(100) REFERENCES ledger.general_chart_of_accounts(subsidiary_shortname) ON UPDATE CASCADE, posted TIMESTAMP);
CREATE TABLE system.general_journal_item (id SERIAL PRIMARY KEY, journal_id INT REFERENCES system.journal ON DELETE CASCADE ON UPDATE CASCADE, description VARCHAR(250) NOT NULL, account_id VARCHAR(10) REFERENCES ledger.general_chart_of_accounts ON UPDATE CASCADE, amount NUMERIC NOT NULL, drcr VARCHAR(2) REFERENCES system.journal_item_type);
CREATE TABLE system.tax_page (id INT PRIMARY KEY, iso_country_code VARCHAR(2) NOT NULL, also_applicable_in VARCHAR(2)[]);
CREATE TABLE system.transaction_tax (id SERIAL PRIMARY KEY, shortname VARCHAR(10), name VARCHAR(250), tax_page_id INT, rate NUMERIC, obsolete TIMESTAMP, included_in_unit_price_default BOOLEAN, track_on_purchases BOOLEAN);
CREATE TABLE system.linked_account (transaction_tax_shortname VARCHAR(10), transaction_type VARCHAR(100), account_id VARCHAR(10) REFERENCES ledger.general_chart_of_accounts, PRIMARY KEY (transaction_tax_shortname, transaction_type));

CREATE TABLE system.report_formula (id INTEGER PRIMARY KEY,	description VARCHAR(100),	formula VARCHAR(1000));
CREATE TABLE system.report (id SERIAL PRIMARY KEY,  name VARCHAR(60) NOT NULL,  shortname VARCHAR(20), description VARCHAR(60) NOT NULL,  default_formula_id INTEGER REFERENCES system.report_formula(id), isincl_date BOOLEAN);
CREATE TABLE system.report_section (id SERIAL PRIMARY KEY, report_id INTEGER REFERENCES system.report(id),	name VARCHAR(60) NOT NULL,	insert_position_id INTEGER);
CREATE TABLE system.report_detail (id SERIAL PRIMARY KEY, report_id INT REFERENCES system.report(id) NOT NULL, 	section_id INT, 	name VARCHAR(60) NOT NULL,   params VARCHAR(60),	item_flag INTEGER,report_formula_id INTEGER,order_num INTEGER );
CREATE TABLE system.report_result (report_id INTEGER REFERENCES system.report(id),	item_id INTEGER,value   NUMERIC);		
INSERT INTO system.journal_item_type VALUES ('DR');
INSERT INTO system.journal_item_type VALUES ('CR');
INSERT INTO system.account_type (id, sum, ord) VALUES ('ASSET', 'DR', 1);
INSERT INTO system.account_type (id, sum, ord) VALUES ('XASSET', 'CR', 2);
INSERT INTO system.account_type (id, sum, ord) VALUES ('LIABILITY', 'CR', 3);
INSERT INTO system.account_type (id, sum, ord) VALUES ('EXPENSE', 'DR', 6);
INSERT INTO system.account_type (id, sum, ord) VALUES ('REVENUE', 'CR', 5);
INSERT INTO system.account_type (id, sum, ord) VALUES ('OWNER_EQUITY', 'CR', 4);

INSERT INTO system.report_formula VALUES(1,'balance',E'SELECT sum(amount) as sumamount FROM (SELECT bal(\\\'general_\\\'||id) AS amount FROM ledger.general_chart_of_accounts %s ORDER BY id) as amount');
INSERT INTO system.report_formula VALUES(2,'total asset','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 1 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(1,2)');
INSERT INTO system.report_formula VALUES(3,'total liabilities','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 1 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(3,4)');
INSERT INTO system.report_formula VALUES(4,'net asset','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 1 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(1,2,3,4)');
INSERT INTO system.report_formula VALUES(5,'total equity','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 1 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(5)');
INSERT INTO system.report_formula VALUES(6,'profit',E'SELECT sum(amount) as sumamount FROM (SELECT profit_bal(\\\'general_\\\'||id,%s,%s) AS amount FROM ledger.general_chart_of_accounts %s ORDER BY id) as amount');
INSERT INTO system.report_formula VALUES(7,'gross profit','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 2 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(6)');
INSERT INTO system.report_formula VALUES(8,'net profit before tax','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 2 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(7)');
INSERT INTO system.report_formula VALUES(9,'net profit after tax','SELECT coalesce(sum(value),0) FROM system.report_result a,system.report_detail b WHERE b.report_id = 2 AND a.item_id = b.id AND b.item_flag = 1 AND b.section_id in(8)');
INSERT INTO system.report_formula VALUES(10,'tax',E'SELECT sum(amount) as sumamount FROM (SELECT tax_bal(\\\'general_\\\'||id,%s,%s) AS amount FROM ledger.general_chart_of_accounts %s ORDER BY id) as amount');

INSERT INTO system.report (name, shortname, description, default_formula_id, isincl_date) VALUES ('Balance Sheet Report', 'bsr', 'report of balance sheet',1,false);
INSERT INTO system.report (name, shortname, description, default_formula_id, isincl_date) VALUES ('Profit & Loss Report', 'plr', 'report of profit & loss',6,true);
INSERT INTO system.report (name, shortname, description, default_formula_id, isincl_date) VALUES ('Tax Report', 'tr', 'report of tax',10,true);

INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(1,'CURRENT ASSETS',4);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(1,'NON-CURRENT ASSETS',9);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(1,'CURRENT LIABILITIES',15);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(1,'NON-CURRENT LIABILITIES',22);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(1,'EQUITY',28);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(2,'Gross profit',0);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(2,'Net profit before tax',6);
INSERT INTO system.report_section (report_id,name,insert_position_id) VALUES(2,'Net profit after tax',9);
   
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,1,'CURRENT ASSETS',null,0,null,1);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,1,'Cash',null,1,1,2);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,1,'Receivables',null,1,1,3);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,1,'Inventory',null,1,1,4);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,1,'Other',null,1,1,5);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'NON-CURRENT ASSETS',null,0,null,6);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'Receivables',null,1,1,7);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'Investments',null,1,1,8);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'LAND',null,1,1,9);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'Property, plant & equipment',null,1,1,10);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'Deferred tax asset',null,1,1,11);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,2,'Total assets',null,2,2,12);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'CURRENT LIABILITIES',null,0,null,13);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'Accounts payable',null,1,1,14);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'Interest bearing liabilities',null,1,1,15);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'Accrued expenses',null,1,1,16); 
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'Provision for current tax',null,1,1,17); 
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,3,'Other',null,1,1,18); 

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'NON-CURRENT LIABILITIES',null,0,null,19);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'Interest bearing liabilities',null,1,1,20);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'Deferred tax liabilities',null,1,1,21);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'Provisions',null,1,1,22);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'Total liabilities',null,2,3,23);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,4,'Net assets',null,2,4,24);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,5,'EQUITY',null,0,null,25);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,5,'Contributed equity',null,1,1,26);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,5,'Reserves',null,1,1,27);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,5,'Retained earnings',null,1,1,28);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(1,5,'Total equity',null,2,5,29);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,6,'Revenue from sales',null,1,6,1);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,6,'Cost of sales',null,1,6,2);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,6,'Gross profit',null,2,7,3);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,7,'Administration',null,1,6,4);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,7,'Interest income',null,1,6,5);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,7,'Interest expense',null,1,6,6);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,7,'Net profit before tax',null,2,8,7);

INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,8,'Current income tax expense',null,1,6,8);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,8,'Deferred income tax expense',null,1,6,9);
INSERT INTO system.report_detail (report_id,section_id,name,params,item_flag,report_formula_id,order_num) VALUES(2,8,'Net profit after tax',null,2,9,10);

--business tables
CREATE TABLE system.entity_type (id VARCHAR(60) PRIMARY KEY);
CREATE TABLE system.entity (id SERIAL PRIMARY KEY, type VARCHAR(60) NOT NULL REFERENCES system.entity_type, name VARCHAR(100), given_name VARCHAR(100),status  varchar(20) default 'ACTIVE');
CREATE TABLE system.contact_detail (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, type VARCHAR(30), detail VARCHAR(100));
CREATE TABLE system.address (id SERIAL PRIMARY KEY, line1 VARCHAR(100) NOT NULL, line2 VARCHAR(100), suburb VARCHAR(50), region VARCHAR(50) NOT NULL, code VARCHAR(10), country VARCHAR(50) NOT NULL);
CREATE TABLE system.location (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, name VARCHAR(50), phone VARCHAR(20), fax VARCHAR(20), address_id INT REFERENCES system.address, deactivated TIMESTAMP);
CREATE TABLE system.relationship (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, rel_entity_id INT REFERENCES system.entity, type VARCHAR(30), location_id INT REFERENCES system.location);
CREATE TABLE contact_log (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, comment VARCHAR(250), filename VARCHAR(100), log_date DATE DEFAULT CURRENT_DATE, action VARCHAR(100), closed DATE, UNIQUE(entity_id, log_date, comment, action));
CREATE TABLE sales_agent (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, commission INT);
CREATE TABLE customer (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, sales_agent_id INT REFERENCES sales_agent, address_id INT REFERENCES system.address, billing_method VARCHAR(20));
CREATE TABLE supplier (id SERIAL PRIMARY KEY, entity_id INT REFERENCES system.entity, address_id INT REFERENCES system.address);

INSERT INTO system.entity_type VALUES ('person');
INSERT INTO system.entity_type VALUES ('company');
INSERT INTO system.entity_type VALUES ('non-profit org');
INSERT INTO system.entity_type VALUES ('government department');

-- more financial tables
CREATE TABLE system.invoice (id BIGSERIAL PRIMARY KEY, date TIMESTAMP, terms SMALLINT DEFAULT system.default_inv_terms(), sales_agent_id INT REFERENCES sales_agent, journal_id BIGINT);
CREATE TABLE system.invoice_item (id BIGSERIAL PRIMARY KEY, invoice_id BIGINT REFERENCES system.invoice ON DELETE CASCADE ON UPDATE CASCADE, qty INT, description VARCHAR(250), unit_price NUMERIC);
CREATE TABLE system.invoice_item_tax (invoice_item_id BIGINT references system.invoice_item, transaction_tax_id INT REFERENCES system.transaction_tax, included_in_unit_price BOOLEAN, PRIMARY KEY (invoice_item_id, transaction_tax_id));
CREATE TABLE system.invoice_delivery_report (id BIGSERIAL PRIMARY KEY, invoice_id BIGINT REFERENCES system.invoice ON DELETE CASCADE ON UPDATE CASCADE,date TIMESTAMP DEFAULT 'NOW',method VARCHAR(20) NOT NULL,address_id INT REFERENCES system.address ON DELETE CASCADE ON UPDATE CASCADE,detail VARCHAR(100));
CREATE TABLE system.receipt (id BIGSERIAL PRIMARY KEY, date TIMESTAMP, invoice_id BIGINT[], journal_id BIGINT REFERENCES system.journal);
CREATE TABLE system.purchase_order (id BIGSERIAL PRIMARY KEY, date TIMESTAMP, supplier_id INT REFERENCES supplier, expense_account_id VARCHAR(10) REFERENCES ledger.general_chart_of_accounts,delivery_location_id INT REFERENCES system.location);
CREATE TABLE system.purchase_order_item (id BIGSERIAL PRIMARY KEY, purchase_order_id BIGINT REFERENCES system.purchase_order, qty INT, description VARCHAR(250), unit_price NUMERIC);
CREATE TABLE system.purchase_item_tax (purchase_order_item_id BIGINT REFERENCES system.purchase_order_item, transaction_tax_id INT REFERENCES system.transaction_tax(id), included_in_unit_price BOOLEAN, PRIMARY KEY(purchase_order_item_id,transaction_tax_id));
CREATE TABLE system.bill_payment (id BIGSERIAL PRIMARY KEY, date TIMESTAMP, purchase_order_id BIGINT[], journal_id BIGINT REFERENCES system.journal);

-- the rest
CREATE VIEW system.pk_lookup AS SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE ordinal_position = 1 AND table_schema NOT IN ('information_schema', 'pg_catalog');
CREATE VIEW system.data_index AS SELECT '<a href=\"/bin/admin.py/show_tables?t=' || table_schema || '.' || table_name || '$query_string$' ||'\">' || table_name || '</a>' AS table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
CREATE VIEW system.info_index AS SELECT '<a href=\"/bin/av.py?v=' || table_schema || '.' || table_name || '$query_string$' ||'\">' || table_name || '</a>' AS view_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'VIEW';
CREATE VIEW system.company_location_info (id, company_name, name, line1, line2, suburb, region, code, country,phone, fax, company_id) AS SELECT l.id,e.name AS company_name,l.name,a.line1,a.line2,INITCAP(a.suburb),UPPER(a.region),a.code,UPPER(a.country),l.phone,l.fax,e.id AS company_id FROM (SELECT * FROM system.entity WHERE type<>'person') e INNER JOIN system.location l ON e.id=l.entity_id LEFT JOIN system.address a ON l.address_id=a.id WHERE l.deactivated IS NULL ORDER BY e.name;
CREATE VIEW contact_info AS SELECT c.name AS company_name, p.given_name, p.name AS surname, cdpos.detail as position, CASE WHEN cdphone.detail IS NOT NULL THEN cdphone.detail ELSE l.phone END AS phone, CASE WHEN cdfax.detail IS NOT NULL THEN cdfax.detail ELSE l.fax END AS fax, cdemail.detail AS email, cdmob.detail AS mobile, cdsms.detail AS sms, c.id AS company_id, p.id AS person_id,CASE WHEN customer.id IS NOT NULL THEN true ELSE false END AS iscustomer,CASE WHEN supplier.id IS NOT NULL THEN true ELSE false END AS issupplier FROM system.entity p LEFT JOIN system.relationship r ON r.entity_id = p.id AND r.type = 'employee' LEFT JOIN system.location l ON r.location_id = l.id AND l.deactivated IS NULL AND l.name NOT IN (SELECT code_value FROM system.sys_code WHERE code_id = 'alt_location_type') LEFT JOIN system.entity c ON r.rel_entity_id = c.id LEFT JOIN system.contact_detail cdpos ON cdpos.entity_id = p.id AND cdpos.type = 'position' LEFT JOIN system.contact_detail cdphone ON cdphone.entity_id = p.id AND cdphone.type = 'phone' LEFT JOIN system.contact_detail cdfax ON cdfax.entity_id = p.id AND cdfax.type = 'fax' LEFT JOIN (select * from system.contact_detail where id in(select min(id) from system.contact_detail group by entity_id,type)) cdemail ON cdemail.entity_id = p.id AND cdemail.type = 'email' LEFT JOIN system.contact_detail cdmob ON cdmob.entity_id = p.id AND cdmob.type = 'mobile' LEFT JOIN system.contact_detail cdsms ON cdsms.entity_id = p.id AND cdsms.type = 'sms' LEFT JOIN customer ON customer.entity_id = p.id LEFT JOIN supplier ON supplier.entity_id = p.id WHERE p.type = 'person' and p.status = 'ACTIVE' ORDER BY company_name, given_name, surname;
CREATE VIEW sales_agent_info AS SELECT s.id, CASE WHEN e.type<>'person' THEN name ELSE NULL END AS name,given_name, CASE WHEN e.type='person' THEN name ELSE NULL END AS surname,s.commission FROM sales_agent s, system.entity e WHERE s.entity_id = e.id ORDER BY name, given_name, surname;
CREATE VIEW system.business_objects AS SELECT DISTINCT vtu.table_schema, RTRIM(vtu.table_name, '_info') AS table_name FROM information_schema.view_table_usage vtu, information_schema.views v WHERE vtu.view_schema = v.table_schema AND vtu.view_name = RTRIM(v.table_name, '_info') AND vtu.view_name = RTRIM(vtu.table_name, '_info') AND vtu.view_schema = 'system' ORDER BY vtu.table_schema, table_name;
CREATE OR REPLACE VIEW customer_billing_info AS SELECT c.id,c.billing_method,c.entity_id, COALESCE(co.name, ((p.given_name::text || ' '::text) || p.name::text)::character varying) AS customer_name, a.line1, a.line2, a.suburb, a.region, a.code, a.country, sai.name, sai.given_name, sai.surname FROM customer c LEFT JOIN sales_agent_info sai ON sai.id = c.sales_agent_id LEFT JOIN "system".address a ON c.address_id=a.id,system.entity e LEFT JOIN (SELECT id ,name,given_name FROM system.entity WHERE type ='person') p ON p.id = e.id LEFT JOIN (SELECT id ,name,given_name FROM system.entity WHERE type <>'person') co ON co.id = e.id WHERE c.entity_id =e.id ORDER BY c.id;
CREATE VIEW supplier_billing_info AS SELECT sp.id, COALESCE(co.name, ((p.given_name::text || ' '::text) || p.name::text)::character varying) AS supplier_name, a.line1, a.line2, a.suburb, a.region, a.code, a.country FROM supplier sp, "system".address a,system.entity e LEFT JOIN (SELECT id ,name,given_name FROM system.entity WHERE type ='person') p ON p.id = e.id LEFT JOIN (SELECT id ,name,given_name FROM system.entity WHERE type <>'person') co ON co.id = e.id WHERE  sp.address_id = a.id AND sp.entity_id =e.id ORDER BY sp.id;
CREATE VIEW system.abn_info AS SELECT e.id as entity_id, cdabn.detail as abn FROM system.entity e LEFT JOIN system.contact_detail cdabn ON cdabn.entity_id = e.id AND cdabn.type = 'abn' ORDER BY e.id;
CREATE VIEW system.person_location_info AS SELECT l.id, given_name||' '||e.name AS person_name, 'place of business' AS type, line1, line2, suburb, region, code, country, CASE WHEN cdphone.detail IS NULL THEN l.phone ELSE cdphone.detail END AS phone, CASE WHEN cdfax.detail IS NULL THEN l.fax ELSE cdfax.detail END AS fax, e.id as person_id FROM system.location l, system.relationship r, system.address a, system.entity e LEFT JOIN system.contact_detail cdphone ON cdphone.entity_id = e.id AND cdphone.type = 'phone' LEFT JOIN system.contact_detail cdfax ON cdfax.entity_id = e.id AND cdfax.type = 'fax' WHERE r.location_id = l.id AND r.entity_id = e.id AND l.address_id = a.id AND r.type = 'employee' AND l.deactivated IS NULL UNION SELECT l.id, given_name||' '||e.name AS person_name, l.name AS type, line1, line2, suburb, region, code, country, l.phone, l.fax, e.id as person_id FROM system.location l, system.entity e, system.address a WHERE l.entity_id = e.id AND l.address_id = a.id AND e.type = 'person' AND l.deactivated IS NULL ORDER BY person_name, type;
CREATE OR REPLACE VIEW customer_info AS SELECT c.id,c.billing_method, CASE WHEN e.type='person' THEN given_name||' '||name ELSE name END AS name, type, cd.abn, line1, line2, suburb, region, code, country, CASE WHEN e.type='person' THEN e.id ELSE NULL END AS person_id, CASE WHEN e.type<>'person' THEN e.id ELSE NULL END AS company_id FROM customer c LEFT JOIN system.address a ON c.address_id=a.id, system.entity e, system.abn_info cd WHERE c.entity_id = e.id AND cd.entity_id= e.id ORDER BY name;
CREATE VIEW system.customer (value, label) AS SELECT id, name FROM customer_info;
CREATE VIEW supplier_info AS SELECT c.id, CASE WHEN e.type='person' THEN given_name||' '||name ELSE name END AS name, type, cd.abn, line1, line2, suburb, region, code, country, CASE WHEN e.type='person' THEN e.id ELSE NULL END AS person_id, CASE WHEN e.type<>'person' THEN e.id ELSE NULL END AS company_id FROM supplier c, system.address a, system.entity e, system.abn_info cd WHERE c.entity_id = e.id AND c.address_id = a.id AND cd.entity_id= e.id ORDER BY name;
CREATE VIEW system.supplier (value, label) AS SELECT id, name FROM supplier_info;

CREATE FUNCTION find_name(text) RETURNS VARCHAR(100) AS $$
DECLARE
	result VARCHAR(100);
BEGIN
	EXECUTE 'SELECT label FROM system.'||SPLIT_PART(SUBSTR($1, 2), ':', 1)||' WHERE value = ' || SPLIT_PART($1, ':', 2) INTO result;
	RETURN result;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 'not found';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ledger.create_sub (
	p_shortname TEXT,
	p_control_id TEXT) RETURNS INT AS $$
BEGIN
	EXECUTE 'CREATE TABLE ledger.' || p_shortname || '_chart_of_accounts (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL)';
	EXECUTE 'CREATE TABLE system.' || p_shortname || '_journal_item (id BIGSERIAL PRIMARY KEY, journal_id BIGINT REFERENCES system.journal ON DELETE CASCADE ON UPDATE CASCADE, description VARCHAR(250) NOT NULL, account_id INT REFERENCES ledger.' || p_shortname || '_chart_of_accounts ON UPDATE CASCADE, amount NUMERIC NOT NULL, drcr VARCHAR(2) REFERENCES system.journal_item_type)';
	EXECUTE 'CREATE VIEW ledger.' || p_shortname || '_info AS SELECT ''' || p_control_id || '.'' || LPAD(id::TEXT, 5, ''0'') AS id, CASE WHEN name LIKE ''#%'' THEN find_name(name) ELSE name END AS name FROM ledger.' || p_shortname || '_chart_of_accounts ORDER BY id';
	EXECUTE $cj$
		CREATE OR REPLACE FUNCTION create_$cj$ || p_shortname || $cj$_journal (	
			p_description		TEXT[],
			p_account_id		TEXT[],
			p_amount		TEXT[],
			p_drcr			TEXT[],
			p_sl_description	TEXT[],
			p_sl_account_id		TEXT[],
			p_sl_amount		TEXT[],
			p_sl_drcr		TEXT[],
			p_post			BOOLEAN	) RETURNS INT AS $journal$
		DECLARE
			v_bal NUMERIC := 0;
			v_journal_id BIGINT;
			v_success_message INT := 30300;
			v_error INT;
		BEGIN
			-- We are assuming that all the general ledger (p_) item arrays are the same length,
			-- and that all the subledger (p_sl_ ) item arrays are the same length. Ultimately,
			-- it would be good to verify these facts.

			-- make sure the journal is balanced
			FOR i IN array_lower(p_amount, 1)..array_upper(p_amount, 1) LOOP
				IF p_drcr[i] = 'CR' THEN
					v_bal := v_bal + CAST(p_amount[i] AS NUMERIC);
				ELSE
					v_bal := v_bal - CAST(p_amount[i] AS NUMERIC);
				END IF;
			END LOOP;
			IF v_bal != 0 THEN
				v_error = 30301;
				RAISE EXCEPTION 'journal not balanced';
			END IF;

			-- We are assuming that the GL items are being written to accounts that allow posting,
			-- and that if there are subledger items, there is exactly one control account entry
			-- equalling their sum. Ultimately, it would be good to verify these facts.

			-- create the journal and get the id
			SELECT INTO v_journal_id nextval('system.journal_id_seq');
            INSERT INTO system.journal (id,ledger_shortname, date) VALUES (v_journal_id,'$cj$ || p_shortname || $cj$', 'NOW');

			-- create the general journal items
			FOR i IN array_lower(p_description, 1)..array_upper(p_description, 1) LOOP
				INSERT INTO system.general_journal_item (journal_id, description, account_id, amount, drcr) VALUES (v_journal_id , p_description[i] , p_account_id[i] , CAST(p_amount[i] AS NUMERIC), p_drcr[i] );
			END LOOP;

			-- create the subledger journal items (if there are any)
            FOR i IN array_lower(p_sl_description, 1)..array_upper(p_sl_description, 1) LOOP
                INSERT INTO system.$cj$ || p_shortname || $cj$_journal_item (journal_id, description, account_id, amount, drcr) VALUES (v_journal_id , p_sl_description[i] , CAST(p_sl_account_id[i] AS INT), CAST(p_sl_amount[i] AS NUMERIC), p_sl_drcr[i] );
            END LOOP;

			-- post the journal (if directed)
			IF p_post THEN
				SELECT INTO v_error post_journal(v_journal_id);
				IF v_error % 100 != 0 THEN
					RAISE EXCEPTION 'called function returned error';
				END IF;
				v_success_message := 30400;
			END IF;
			RETURN v_success_message;
		EXCEPTION
			WHEN RAISE_EXCEPTION THEN
				RETURN v_error;
			WHEN OTHERS THEN
				RETURN 30399;
		END;
		$journal$ LANGUAGE plpgsql;$cj$;
	IF p_shortname = 'ar' THEN
		EXECUTE 'CREATE OR REPLACE VIEW system.invoice_info AS SELECT DISTINCT i.id, date, terms, cu.id AS customer_id,cu.billing_method,find_name(coa.name) AS customer_name,address_id,abn,COALESCE(system.invoice_total_ex_tax(i.id),round(0,2)) AS total_ex,COALESCE(system.invoice_tax(i.id),round(0,2)) AS tax,COALESCE(system.invoice_total(i.id),round(0,2)) AS total_inc FROM system.invoice i,system.abn_info c,customer cu,system.ar_journal_item ji,ledger.ar_chart_of_accounts coa WHERE i.journal_id = ji.journal_id AND ji.account_id = coa.id AND link_object_id(coa.name) = cu.id AND link_object(coa.name) = ''customer'' AND cu.entity_id = c.entity_id';
	END IF;
	IF p_shortname = 'ap' THEN
		EXECUTE 'CREATE VIEW system.purchase_order_info AS SELECT DISTINCT po.id,po.delivery_location_id, date, s.id AS supplier_id,find_name(coa.name) AS supplier_name,address_id,abn,COALESCE(system.purchase_order_total_ex_tax(po.id),round(0,2)) AS total_ex,COALESCE(system.purchase_order_tax(po.id),round(0,2)) AS tax,COALESCE(system.purchase_order_total(po.id),round(0,2)) AS total_inc FROM system.purchase_order po,system.abn_info a,supplier s, ledger.ap_chart_of_accounts coa WHERE po.supplier_id = s.id AND link_object_id(coa.name) = s.id AND link_object(coa.name) = ''supplier'' AND s.entity_id = a.entity_id';	
	END IF;
	RETURN 0;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 99999;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_account (
	p_ledger TEXT,
	p_id TEXT,
	p_name TEXT,
	p_account_type_id TEXT,
	p_subsidiary_shortname TEXT) RETURNS INT AS $$
DECLARE
	v_error		 INT;
	v_id			INT;
BEGIN
	IF EXISTS (SELECT id,name FROM ledger.general_chart_of_accounts WHERE id =p_id OR name =p_name) THEN
		RETURN 30101;
	END IF;
	IF p_ledger NOTNULL AND p_ledger<>'' THEN
		EXECUTE 'INSERT INTO ledger.' || p_ledger || '_chart_of_accounts (name) VALUES (''' || p_name || ''')';
		EXECUTE 'SELECT id FROM ledger.'|| p_ledger ||'_chart_of_accounts WHERE name = ''' || p_name || '''' INTO v_id;
		EXECUTE 'CREATE VIEW ledger.' || p_ledger || '_' || v_id || ' AS SELECT j.id AS journal_id, date, description, (SELECT amount FROM system.' || p_ledger || '_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''DR'') AS dr, (SELECT amount FROM system.' || p_ledger || '_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''CR'') AS cr FROM system.journal j, system.' || p_ledger || '_journal_item ji WHERE j.id = ji.journal_id AND posted NOTNULL AND account_id = ''' || v_id || '''';
	ELSE
		IF p_subsidiary_shortname IS NULL OR p_subsidiary_shortname = '' THEN
			EXECUTE 'INSERT INTO ledger.general_chart_of_accounts (id, name, account_type_id, subsidiary_shortname, opening_balance) VALUES (''' || p_id || ''', ''' || p_name || ''', ''' || p_account_type_id || ''', NULL, NULL)';
			EXECUTE 'CREATE VIEW ledger.general_' || p_id || ' AS SELECT j.id AS journal_id, date, description, (SELECT amount FROM system.general_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''DR'') AS dr, (SELECT amount FROM system.general_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''CR'') AS cr FROM system.journal j, system.general_journal_item ji WHERE j.id = ji.journal_id AND posted NOTNULL AND account_id = ''' || p_id || '''';
		ELSE
			EXECUTE 'INSERT INTO ledger.general_chart_of_accounts (id, name, account_type_id, subsidiary_shortname, opening_balance) VALUES (''' || p_id || ''', ''' || p_name || ''', ''' || p_account_type_id || ''', ''' || p_subsidiary_shortname || ''', 0)';
			EXECUTE 'CREATE VIEW ledger.general_' || p_id || ' AS SELECT j.id AS journal_id, date, description, (SELECT amount FROM system.general_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''DR'') AS dr, (SELECT amount FROM system.general_journal_item ji_sub WHERE ji_sub.id = ji.id AND drcr = ''CR'') AS cr FROM system.journal j, system.general_journal_item ji WHERE j.id = ji.journal_id AND posted NOTNULL AND account_id = ''' || p_id || '''';
			SELECT INTO v_error ledger.create_sub(p_subsidiary_shortname, p_id);
			IF v_error % 100 != 0 THEN
				RAISE EXCEPTION 'called function returned error';
			END IF;
		END IF;
	END IF;
	RETURN 30100;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 30199;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_subledger (p_shortname TEXT,p_control_id TEXT) RETURNS INT AS $$
DECLARE
	v_error INT;
BEGIN
	EXECUTE 'UPDATE ledger.general_chart_of_accounts SET subsidiary_shortname = ''' || p_shortname || ''' WHERE id = ''' || p_control_id || '''';
	SELECT INTO v_error ledger.create_sub(p_shortname, p_control_id);
	IF v_error % 100 != 0 THEN
		RAISE EXCEPTION 'called function returned error';
	END IF;
	RETURN 30600;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 30699;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_journal (	
	p_description		TEXT[],
	p_account_id		TEXT[],
	p_amount		TEXT[],
	p_drcr			TEXT[],
	p_sl_description	TEXT[],
	p_sl_account_id		TEXT[],
	p_sl_amount		TEXT[],
	p_sl_drcr		TEXT[],
	p_post			BOOLEAN	) RETURNS INT AS $$
DECLARE
	v_bal NUMERIC := 0;
	v_journal_id BIGINT;
	v_success_message INT := 30300;
	v_error INT;
BEGIN
	-- We are assuming that all the general ledger (p_) item arrays are the same length,
	-- and that all the subledger (p_sl_ ) item arrays are the same length. Ultimately,
	-- it would be good to verify these facts.

	-- make sure the journal is balanced
	FOR i IN array_lower(p_amount, 1)..array_upper(p_amount, 1) LOOP
		IF p_drcr[i] = 'CR' THEN
			v_bal := v_bal + CAST(p_amount[i] AS NUMERIC);
		ELSE
			v_bal := v_bal - CAST(p_amount[i] AS NUMERIC);
		END IF;
	END LOOP;
	IF v_bal != 0 THEN
		v_error = 30301;
		RAISE EXCEPTION 'journal not balanced';
	END IF;

	-- We are assuming that the GL items are being written to accounts that allow posting,
	-- and that if there are subledger items, there is exactly one control account entry
	-- equalling their sum. Ultimately, it would be good to verify these facts.

	-- create the journal and get the id
	SELECT INTO v_journal_id nextval('system.journal_id_seq');
	INSERT INTO system.journal (id, date) VALUES (v_journal_id, 'NOW');

	-- create the general journal items
	FOR i IN array_lower(p_description, 1)..array_upper(p_description, 1) LOOP
		INSERT INTO system.general_journal_item (journal_id, description, account_id, amount, drcr) VALUES (v_journal_id , p_description[i] , p_account_id[i] , CAST(p_amount[i] AS NUMERIC), p_drcr[i] );
	END LOOP;

	-- post the journal (if directed)
	IF p_post THEN
		SELECT INTO v_error post_journal(v_journal_id);
		IF v_error % 100 != 0 THEN
			RAISE EXCEPTION 'called function returned error';
		END IF;
		v_success_message := 30400;
	END IF;
	RETURN v_success_message;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 30399;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION edit_journal (
	p_journal_id INT,
	p_old_g_item_id INT[],
	p_old_g_amount INT,
	p_old_g_description TEXT,
	p_old_d_item_id INT[],
	p_description TEXT[],
	p_account_id TEXT[],
	p_amount TEXT[],
	p_drcr TEXT[],
	p_subledger TEXT,
	p_sl_description TEXT[],
	p_sl_account_id TEXT[],
	p_sl_amount TEXT[],
	p_sl_drcr TEXT[],
	p_post BOOLEAN) RETURNS INT AS $$
DECLARE
	v_old_g_item_id TEXT[];
	v_old_d_item_id TEXT[];
	v_control_item_id INT;
	v_control_item_drcr TEXT;
	v_item RECORD;
	v_bal NUMERIC := 0;
	v_journal_id BIGINT;
	v_success_message INT := 30500;
	v_error INT;
	v_flag INT;
	v_num INT;
BEGIN
	-- We are assuming that all the general ledger (p_) item arrays are the same length,
	-- and that all the subledger (p_sl_ ) item arrays are the same length. Ultimately,
	-- it would be good to verify these facts.
	
	--the journal exists or not
	IF p_journal_id IS NULL THEN
		v_error = 30302;
		RAISE EXCEPTION 'journal not exists';
	ELSE
		v_journal_id := p_journal_id;
	END IF;
	-- make sure the journal is balanced
	v_num :=1;
	FOR v_item IN SELECT id FROM system.general_journal_item WHERE journal_id=v_journal_id LOOP
		v_old_g_item_id[v_num] := v_item.id;
		v_num := v_num+1;
	END LOOP;		
	
	IF p_subledger NOTNULL AND p_subledger<>'' THEN
		SELECT gj.id INTO v_control_item_id FROM system.general_journal_item gj,system.journal j,ledger.general_chart_of_accounts gc WHERE gj.journal_id=j.id AND j.ledger_shortname = gc.subsidiary_shortname AND gj.account_id=gc.id AND j.id=v_journal_id;
		IF array_upper(p_old_g_item_id, 1) IS NOT NULL THEN
			FOR i IN array_lower(p_old_g_item_id, 1)..array_upper(p_old_g_item_id, 1) LOOP
				IF p_old_g_item_id[i]=v_control_item_id THEN						
					UPDATE system.general_journal_item SET amount = p_old_g_amount,description=p_old_g_description WHERE id=v_control_item_id;
					EXIT;
				END IF;
			END LOOP;
			FOR i IN array_lower(v_old_g_item_id, 1)..array_upper(v_old_g_item_id, 1) LOOP				   
				v_flag := 0;
				FOR j IN array_lower(p_old_g_item_id, 1)..array_upper(p_old_g_item_id, 1) LOOP		   
					IF v_old_g_item_id[i]=p_old_g_item_id[j] THEN
						v_flag :=1;
						EXIT;
					END  IF;
				END LOOP;
				IF v_flag=0 THEN
					DELETE FROM system.general_journal_item WHERE id=v_old_g_item_id[i];
				END IF;
			END LOOP;
		ELSE
			FOR i IN array_lower(v_old_g_item_id, 1)..array_upper(v_old_g_item_id, 1) LOOP
				DELETE FROM system.general_journal_item WHERE id=v_old_g_item_id[i];
			END LOOP;
		END IF;
	ELSE
		IF array_upper(p_old_g_item_id, 1) IS NOT NULL THEN
			FOR i IN array_lower(v_old_g_item_id, 1)..array_upper(v_old_g_item_id, 1) LOOP				   
				v_flag := 0;
				FOR j IN array_lower(p_old_g_item_id, 1)..array_upper(p_old_g_item_id, 1) LOOP		   
					IF v_old_g_item_id[i]=p_old_g_item_id[j] THEN
						v_flag :=1;
						EXIT;
					END  IF;
				END LOOP;
				IF v_flag=0 THEN
					DELETE FROM system.general_journal_item WHERE id=v_old_g_item_id[i];
				END IF;
			END LOOP;
		ELSE
			IF array_upper(v_old_item_id, 1) IS NOT NULL THEN
				FOR i IN array_lower(v_old_item_id, 1)..array_upper(v_old_item_id, 1) LOOP
					DELETE FROM system.general_journal_item WHERE id=v_old_item_id[i];
				END Loop;
			END IF;
		END IF;
	END IF;
	
	IF array_upper(p_amount, 1) IS NOT NULL THEN
		FOR i IN array_lower(p_amount, 1)..array_upper(p_amount, 1) LOOP
			IF p_drcr[i] = 'CR' THEN
				v_bal := v_bal + CAST(p_amount[i] AS NUMERIC);
			ELSE
				v_bal := v_bal - CAST(p_amount[i] AS NUMERIC);
			END IF;
		END LOOP;
		FOR v_item IN SELECT drcr,amount FROM system.general_journal_item WHERE journal_id=v_journal_id LOOP
			IF v_item.drcr='CR' THEN
				v_bal := v_bal + v_item.amount;
			ELSE
				v_bal := v_bal - v_item.amount; 
			END IF;
		END LOOP;
		IF v_bal != 0 THEN
			v_error = 30301;
			RAISE EXCEPTION 'journal not balanced';
		END IF;
	END IF;

	-- We are assuming that the GL items are being written to accounts that allow posting,
	-- and that if there are subledger items, there is exactly one control account entry
	-- equalling their sum. Ultimately, it would be good to verify these facts.

	-- create the general journal items
	IF array_upper(p_description, 1) IS NOT NULL THEN
		FOR i IN array_lower(p_description, 1)..array_upper(p_description, 1) LOOP
			EXECUTE 'INSERT INTO system.general_journal_item (journal_id, description, account_id, amount, drcr) VALUES (' || v_journal_id || ', ''' || p_description[i] || ''', ' || p_account_id[i] || ', ' || p_amount[i] || ', ''' || p_drcr[i] || ''')';
		END LOOP;
	END IF;
	
	SELECT count(*) INTO v_num FROM system.general_journal_item WHERE journal_id=v_journal_id;
	IF v_num=0 THEN
		DELETE FROM system.journal WHERE id = v_journal_id;
	END IF;
	
	-- create the subledger journal items (if there are any)	   
	IF p_subledger NOTNULL AND p_subledger<>'' THEN
		v_num :=1;
		--FOR v_item IN EXECUTE 'SELECT id FROM system. '|| p_subledger ||'_journal_item WHERE journal_id=v_journal_id' LOOP
		FOR v_item IN EXECUTE 'SELECT id FROM system.'|| p_subledger ||'_journal_item WHERE journal_id = '|| v_journal_id LOOP
			v_old_d_item_id[v_num] := v_item.id;
			v_num := v_num+1;
		END LOOP;
		IF array_upper(p_old_d_item_id, 1) IS NOT NULL THEN
			FOR i IN array_lower(v_old_d_item_id, 1)..array_upper(v_old_d_item_id, 1) LOOP				   
				v_flag := 0;
				FOR j IN array_lower(p_old_d_item_id, 1)..array_upper(p_old_d_item_id, 1) LOOP
					IF v_old_d_item_id[i]=p_old_d_item_id[j] THEN
						v_flag :=1;
						EXIT;
					END  IF;
				END LOOP;
				IF v_flag=0 THEN
					EXECUTE 'DELETE FROM system. '|| p_subledger ||'_journal_item WHERE id='||v_old_d_item_id[i]||' AND journal_id = '||v_journal_id;
				END IF;
			END LOOP;
		ELSE
			IF array_upper(v_old_d_item_id, 1) IS NOT NULL THEN
				FOR i IN array_lower(v_old_d_item_id, 1)..array_upper(v_old_d_item_id, 1) LOOP
					EXECUTE 'DELETE FROM system. '|| p_subledger ||'_journal_item WHERE id='||v_old_d_item_id[i]||' AND journal_id ='|| v_journal_id;
				END LOOP;
			END IF;
		END IF;
		
		IF array_upper(p_sl_description, 1) IS NOT NULL THEN
			FOR i IN array_lower(p_sl_description, 1)..array_upper(p_sl_description, 1) LOOP
				EXECUTE 'INSERT INTO system.' || p_subledger || '_journal_item (journal_id, description, account_id, amount, drcr) VALUES (' || v_journal_id || ', ''' || p_sl_description[i] || ''', ' || p_sl_account_id[i] || ', ' || p_sl_amount[i] || ', ''' || p_sl_drcr[i] || ''' )';
			END LOOP;
		END IF;
	END IF;
	
	
	-- post the journal (if directed)
	IF p_post THEN
		SELECT INTO v_error post_journal(v_journal_id);
		IF v_error % 100 != 0 THEN
			RAISE EXCEPTION 'called function returned error';
		END IF;
		v_success_message := 30400;
	END IF;
	RETURN v_success_message;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 30399;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION post_journal (p_journal_id TEXT) RETURNS INT AS $$
BEGIN
	UPDATE system.journal SET posted = 'NOW' WHERE id = p_journal_id;
	RETURN 30400;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 30499;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION bal(text) RETURNS NUMERIC AS $$
DECLARE
	account RECORD;
	account_type VARCHAR(2);
	result NUMERIC;
BEGIN
	EXECUTE 'SELECT COALESCE(SUM(cr), 0) - COALESCE(SUM(dr), 0) AS crbal FROM ledger.'|| quote_ident(regexp_replace($1, E'[0-9]*\\\\.0*', '')) INTO account;
	result := account.crbal;
	if split_part($1, '_', 1) = 'general' then
		EXECUTE 'SELECT sum FROM system.account_type WHERE id = (SELECT account_type_id FROM ledger.general_chart_of_accounts WHERE id = ' || split_part($1, '_', 2) || ')' INTO account_type;
	else
		EXECUTE 'SELECT sum FROM system.account_type WHERE id = (SELECT account_type_id FROM ledger.general_chart_of_accounts WHERE id = ' || split_part(split_part($1, '_', 2), '.', 1) || ')' INTO account_type;
	end if;
	if account_type = 'DR' then
		result := -result;
	end if;
	RETURN result;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION profit_bal(text,text,text)  RETURNS  NUMERIC AS  $$
DECLARE
	account RECORD;
	result NUMERIC;
BEGIN
	EXECUTE 'SELECT COALESCE(SUM(cr), 0) - COALESCE(SUM(dr), 0) AS crbal FROM ledger.' || quote_ident(regexp_replace($1, E'[0-9]*\\\\.0*', '')) || ' WHERE date BETWEEN to_date(''' || $2 || ''',''yyyymmdd'') AND to_date(''' || $3 || ''',''yyyymmdd'')' inTO account;
	result := account.crbal;
	RETURN result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tax_bal(text,text,text)  RETURNS NUMERIC AS $$
DECLARE
	account RECORD;
	result NUMERIC;
BEGIN
	EXECUTE 'SELECT COALESCE(SUM(cr), 0) - COALESCE(SUM(dr), 0) AS crbal FROM ledger.' || quote_ident(regexp_replace($1, E'[0-9]*\\\\.0*', '')) || ' WHERE date BETWEEN to_date(''' || $2 || ''',''yyyymmdd'') AND to_date(''' || $3 || ''',''yyyymmdd'')' inTO account;
	result := account.crbal;
	RETURN result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION balance(
	TEXT,
	begindate TEXT,
	enddte TEXT) RETURNS NUMERIC AS $$
DECLARE
	account RECORD;
	account_type VARCHAR(2);
	result NUMERIC;
BEGIN
	EXECUTE 'SELECT COALESCE(SUM(cr), 0) - COALESCE(SUM(dr), 0) AS crbal FROM ledger.'|| quote_ident(regexp_replace($1, E'[0-9]*\\\\.0*', ''))||' WHERE date BETWEEN '||to_date(COALESCE($2, '1970-01-01'),'YYYY-MM-DD')||' AND '|| to_date(COALESCE($3, '2100-01-01'),'YYYY-MM-DD') INTO account;
	result := account.crbal;
	if split_part($1, '_', 1) = 'general' then
		EXECUTE 'SELECT sum FROM system.account_type WHERE id = (SELECT account_type_id FROM ledger.general_chart_of_accounts WHERE id = ' || split_part($1, '_', 2) || ')' INTO account_type;
	else
		EXECUTE 'SELECT sum FROM system.account_type WHERE id = (SELECT account_type_id FROM ledger.general_chart_of_accounts WHERE id = ' || split_part(split_part($1, '_', 2), '.', 1) || ')' INTO account_type;
	end if;
	if account_type = 'DR' then
		result := -result;
	end if;
	RETURN result;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION link_object_id(text) RETURNS INT AS $$
DECLARE
	result INT;
BEGIN
	result := SPLIT_PART(SPLIT_PART($1, '#', 2), ':', 2);
	RETURN result;
EXCEPTION
	WHEN OTHERS THEN
		RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION link_object(text) RETURNS VARCHAR(20) AS $$
DECLARE
	result VARCHAR(20);
BEGIN
	result := SPLIT_PART(SPLIT_PART($1, '#', 2), ':', 1);
	RETURN result;
EXCEPTION
	WHEN OTHERS THEN
		RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.inv_item_tax (
	p_invoice_item_id BIGINT,
	p_tax_id INT) RETURNS NUMERIC AS $$
DECLARE
	v_tax RECORD;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_tax * FROM system.transaction_tax WHERE id = p_tax_id;
	SELECT INTO v_result (qty * unit_price - system.inv_item_included_tax(p_invoice_item_id)) * COALESCE(v_tax.rate,0) / 100 FROM system.invoice_item WHERE id = p_invoice_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.inv_item_included_tax (p_invoice_item_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_included_tax_rate NUMERIC;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_included_tax_rate COALESCE(sum(rate), 0) FROM system.transaction_tax WHERE id IN (SELECT transaction_tax_id FROM system.invoice_item_tax WHERE invoice_item_id = p_invoice_item_id AND included_in_unit_price);
	SELECT INTO v_result qty * unit_price * v_included_tax_rate / (100 + v_included_tax_rate) FROM system.invoice_item WHERE id = p_invoice_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.inv_item_combined_tax (p_invoice_item_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_tax_rate NUMERIC;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_tax_rate sum(rate) FROM system.transaction_tax WHERE id IN (SELECT transaction_tax_id FROM system.invoice_item_tax WHERE invoice_item_id = p_invoice_item_id);
	SELECT INTO v_result (qty * unit_price - system.inv_item_included_tax(p_invoice_item_id)) * v_tax_rate / 100 FROM system.invoice_item WHERE id = p_invoice_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE VIEW system.invoice_tax_aggregation AS SELECT invoice_id, tt.shortname, round(sum(system.inv_item_tax(ii.id, tt.id)), 2) AS individual_tax_total FROM system.invoice_item ii, system.invoice_item_tax it, system.transaction_tax tt WHERE it.invoice_item_id = ii.id AND it.transaction_tax_id = tt.id GROUP BY invoice_id, shortname;

CREATE OR REPLACE FUNCTION system.invoice_total_ex_tax (p_invoice_id BIGINT) RETURNS NUMERIC as $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result round(sum(item_total_ex_tax), 2) FROM (SELECT invoice_id, qty * unit_price  - COALESCE(system.inv_item_included_tax(id),0) AS item_total_ex_tax FROM system.invoice_item ii WHERE invoice_id = p_invoice_id) AS totals WHERE invoice_id = p_invoice_id GROUP BY invoice_id;
	RETURN v_result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.invoice_tax (p_invoice_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result COALESCE(sum(individual_tax_total),round(0,2)) FROM system.invoice_tax_aggregation WHERE invoice_id = p_invoice_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.invoice_total (p_invoice_id BIGINT) RETURNS NUMERIC as $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result system.invoice_total_ex_tax(p_invoice_id) + system.invoice_tax(p_invoice_id);
	RETURN v_result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_invoice (
	p_customer_id TEXT,
	p_sales_agent_id TEXT,
	p_invoice_id TEXT,
	p_item_qty TEXT[],
	p_item_description TEXT[],
	p_item_unit_price TEXT[],
	p_item_tax_ids TEXT[],
	p_item_tax_inclusions TEXT[]
) RETURNS INT AS $$
DECLARE
	v_invoice_id BIGINT;
	v_journal_id BIGINT;
	v_invoice_item_id BIGINT;
	v_item_tax_ids TEXT[];
	v_item_tax_inclusions TEXT[];
	v_rec RECORD;
	v_special_ac_sales_rev TEXT;
	v_customer_ac INT;
	v_comm_rec RECORD;
	v_commission_tax NUMERIC:=0;
	v_gl_descriptions TEXT[];
	v_gl_account_ids TEXT[];
	v_gl_amounts TEXT[];
	v_gl_drcrs TEXT[];
	v_sl_descriptions TEXT[];
	v_sl_account_ids TEXT[];
	v_sl_amounts TEXT[];
	v_sl_drcrs TEXT[];
	v_error INT;
BEGIN
	-- create the invoice and get the id
	IF p_customer_id IS NULL OR p_customer_id='' OR array_upper(p_item_description, 1) IS NULL THEN
		RAISE EXCEPTION 'Not enough parameters';
	END IF;
	IF p_invoice_id IS NULL THEN
		SELECT INTO v_invoice_id nextval('system.invoice_id_seq');
	ELSE
		v_invoice_id = p_invoice_id;
	END IF;
	INSERT INTO system.invoice (id, date, sales_agent_id) VALUES (v_invoice_id, 'NOW', CAST(p_sales_agent_id AS INT));
	--COMMIT;

	-- We are assuming that all the item arrays are the same length Ultimately,
	-- it would be good to verify this fact.

	-- create the invoice items (and tax records for each item - these are in a
	-- separate table)
	FOR i IN array_lower(p_item_qty, 1)..array_upper(p_item_qty, 1) LOOP
		SELECT INTO v_invoice_item_id nextval('system.invoice_item_id_seq');
		INSERT INTO system.invoice_item (id, invoice_id, qty, description, unit_price) VALUES (v_invoice_item_id, v_invoice_id, CAST(p_item_qty[i] AS INT), p_item_description[i], CAST(p_item_unit_price[i] AS NUMERIC));
		v_item_tax_ids := string_to_array(p_item_tax_ids[i], ',');
		v_item_tax_inclusions := string_to_array(p_item_tax_inclusions[i], ',');
		IF v_item_tax_ids IS NOT NULL THEN
			FOR t in array_lower(v_item_tax_ids, 1)..array_upper(v_item_tax_ids, 1) LOOP
				EXECUTE 'INSERT INTO system.invoice_item_tax (invoice_item_id, transaction_tax_id, included_in_unit_price) VALUES (' || v_invoice_item_id || ', ' || v_item_tax_ids[t] || ', ''' || v_item_tax_inclusions[t] || ''')';
			END LOOP;
		END IF;
	END LOOP;

	-- prepare journal... AR control account
	SELECT INTO v_rec id AS account_id FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname = 'ar';
	v_gl_descriptions[1] := 'Invoice #' || v_invoice_id;
	v_gl_account_ids[1] := CAST(v_rec.account_id AS TEXT);
	v_gl_amounts[1] := CAST(system.invoice_total(v_invoice_id) AS TEXT);
	v_gl_drcrs[1] := CAST('DR'AS TEXT);

	-- prepare journal... get the aggregated transaction taxes
	FOR v_rec IN SELECT CASE WHEN position(shortname in coa.name) > 0 THEN 'Invoice #' || invoice_id ELSE shortname || ' on invoice #' || invoice_id END AS description, account_id, individual_tax_total AS amount FROM system.linked_account la, system.invoice_tax_aggregation ta, ledger.general_chart_of_accounts coa WHERE la.transaction_tax_shortname = ta.shortname AND la.account_id = coa.id AND lower(la.transaction_type) = 'retail sale' AND ta.invoice_id = v_invoice_id GROUP BY invoice_id, shortname, account_id, individual_tax_total, coa.name LOOP
		-- We are assuming that taxes with rates of zero will not have a linked
		-- account and therefore not appear in this result set. Ultimately, it
		-- may be useful to explicitly ignore zero amounts.
		v_gl_descriptions := v_gl_descriptions || v_rec.description;
		v_gl_account_ids := v_gl_account_ids || CAST(v_rec.account_id AS TEXT);
		v_gl_amounts := v_gl_amounts || CAST(v_rec.amount AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);
	END LOOP;

	-- prepare journal... book revenue
	SELECT INTO v_special_ac_sales_rev code_value FROM system.sys_code WHERE code_id = 'special_ac_sales_rev';
	v_gl_descriptions := v_gl_descriptions || CAST('Invoice #' || v_invoice_id AS TEXT);
	v_gl_account_ids := v_gl_account_ids || v_special_ac_sales_rev;
	v_gl_amounts := v_gl_amounts || CAST(system.invoice_total_ex_tax(v_invoice_id) AS TEXT);
	v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);
	
	-- prepare journal... AR ledger
	SELECT INTO v_customer_ac id FROM ledger.ar_chart_of_accounts WHERE name = '#customer:' || p_customer_id;
	v_sl_descriptions[1] := 'Invoice #' || v_invoice_id;
	v_sl_account_ids[1] := CAST(v_customer_ac AS TEXT);
	v_sl_amounts[1] := CAST(system.invoice_total(v_invoice_id) AS TEXT);
	v_sl_drcrs[1] := CAST('DR' AS TEXT);

	-- prepare journal... commission payable
	IF p_sales_agent_id IS NOT NULL THEN
		SELECT INTO v_comm_rec CASE WHEN position('commission' in lower(name)) > 0 THEN COALESCE(name, given_name || ' ' || surname) || ' - Invoice #' || v_invoice_id ELSE COALESCE(name, given_name || ' ' || surname) || ' commission - Invoice #' || v_invoice_id END AS description, code_value AS account_id, round(system.invoice_total(v_invoice_id) * commission / 100, 2) AS amount FROM sales_agent_info, system.sys_code WHERE code_id = 'special_ac_comm_pay' AND id = p_sales_agent_id;
		v_gl_descriptions := v_gl_descriptions || v_comm_rec.description;
		v_gl_account_ids := v_gl_account_ids || CAST(v_comm_rec.account_id AS TEXT);
		v_gl_amounts := v_gl_amounts || CAST(v_comm_rec.amount AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);

		--prepare journal... tax on commission
		FOR v_rec IN SELECT CASE WHEN position(shortname in coa.name) > 0 THEN 'Invoice #' || v_invoice_id || ' commission' ELSE shortname || ' on commission for invoice #' || v_invoice_id END AS description, account_id, round(v_comm_rec.amount * rate / (100 + rate), 2) AS amount FROM system.transaction_tax tt, system.linked_account la, ledger.general_chart_of_accounts coa WHERE la.transaction_tax_shortname = tt.shortname AND la.account_id = coa.id AND lower(la.transaction_type) = 'retail purchase' LOOP
			v_gl_descriptions := v_gl_descriptions || v_rec.description;
			v_gl_account_ids := v_gl_account_ids || CAST(v_rec.account_id AS TEXT);
			v_gl_amounts := v_gl_amounts || CAST(v_rec.amount AS TEXT);
			v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
			v_commission_tax := v_commission_tax + v_rec.amount;
		END LOOP;

		--prepare journal... commission expense
		SELECT INTO v_rec CASE WHEN position('commission' in lower(name)) > 0 THEN 'Invoice #' || v_invoice_id ELSE 'Commission on invoice #' || v_invoice_id END AS description, code_value AS account_id FROM ledger.general_chart_of_accounts, system.sys_code WHERE code_id = 'special_ac_comm_exp' AND code_value = id;
		v_gl_descriptions := v_gl_descriptions || v_rec.description;
		v_gl_account_ids := v_gl_account_ids || CAST(v_rec.account_id AS TEXT);
		v_gl_amounts := v_gl_amounts || CAST(v_comm_rec.amount - v_commission_tax AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
	END IF;
	
	SELECT INTO v_error create_ar_journal(v_gl_descriptions, v_gl_account_ids, v_gl_amounts, v_gl_drcrs, v_sl_descriptions, v_sl_account_ids, v_sl_amounts, v_sl_drcrs, TRUE);
	IF v_error % 100 != 0 THEN
		RAISE EXCEPTION 'called function returned error';
	END IF;
	SELECT INTO v_journal_id currval('system.journal_id_seq');
	UPDATE system.invoice SET journal_id = v_journal_id WHERE id = v_invoice_id;
	RETURN 30200;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 30299;
END;
$$ LANGUAGE plpgsql;

CREATE VIEW system.invoice_item_info AS SELECT invoice_id, description, round(qty * unit_price - COALESCE(system.inv_item_included_tax(ii.id),0), 2) AS total_ex, round(COALESCE(system.inv_item_combined_tax(ii.id),0), 2) as combined_tax, round(qty * unit_price - COALESCE(system.inv_item_included_tax(ii.id),0) + COALESCE(system.inv_item_combined_tax(ii.id),0), 2) as total_inc FROM system.invoice_item ii ORDER BY id;

CREATE TABLE system.expense_report (id SERIAL PRIMARY KEY, journal_id BIGINT REFERENCES system.journal);
CREATE TABLE system.expense_report_item_tax (journal_item_id BIGINT REFERENCES system.general_journal_item, transaction_tax_id INT REFERENCES system.transaction_tax, amount NUMERIC, PRIMARY KEY (journal_item_id, transaction_tax_id));

CREATE OR REPLACE FUNCTION create_expense_report (
	p_expense_report_id TEXT,
	p_expenses_relate_to TEXT,
	p_item_account_id TEXT[],
	p_item_amount TEXT[],
	p_item_description TEXT[],
	p_item_tax_ids TEXT[],
	p_item_tax_amounts TEXT[]) RETURNS INT AS $$
DECLARE
	v_special_ac_cash TEXT;
	v_expense_report_id INT;
	v_expense_report_total NUMERIC:=0;
	v_item_tax_ids TEXT[];
	v_item_tax_amounts TEXT[];
	v_tax_id TEXT[];
	v_tax_amount NUMERIC[];
	v_ind INT;
	v_item_total_tax NUMERIC;
	v_rec RECORD;
	v_journal_id BIGINT;
	v_journal_item_id BIGINT;
	v_gl_descriptions TEXT[];
	v_gl_account_ids TEXT[];
	v_gl_amounts TEXT[];
	v_gl_drcrs TEXT[];
	v_error INT;
BEGIN
	-- create the expense report and get the id
	IF array_upper(p_item_description, 1) IS NULL THEN
		RAISE EXCEPTION 'Nothing to expense';
	END IF;
	IF p_expense_report_id IS NULL THEN
		SELECT INTO v_expense_report_id nextval('system.expense_report_id_seq');
	ELSE
		v_expense_report_id = p_expense_report_id;
	END IF;
	INSERT INTO system.expense_report (id) VALUES (v_expense_report_id);

	-- We are assuming that all the item arrays are the same length. Ultimately,
	-- it would be good to verify this fact.

	-- prepare journal... Cash account
	SELECT INTO v_special_ac_cash code_value FROM system.sys_code WHERE code_id = 'special_ac_cash';
	FOR i IN array_lower(p_item_amount, 1)..array_upper(p_item_amount, 1) LOOP
		v_expense_report_total := v_expense_report_total + CAST(p_item_amount[i] AS NUMERIC);
	END LOOP;
	v_gl_descriptions[1] := 'Expense report #' || v_expense_report_id || COALESCE(' - ' || p_expenses_relate_to, '');
	v_gl_account_ids[1] := v_special_ac_cash;
	v_gl_amounts[1] := CAST(v_expense_report_total AS TEXT);
	v_gl_drcrs[1] := 'CR';

	-- prepare journal... aggregate the transaction taxes
	IF p_item_tax_ids IS NOT NULL THEN
		FOR i IN array_lower(p_item_tax_ids, 1)..array_upper(p_item_tax_ids, 1) LOOP
			v_item_tax_ids := string_to_array(p_item_tax_ids[i], ',');
			v_item_tax_amounts := string_to_array(p_item_tax_amounts[i], ',');
			FOR t IN array_lower(v_item_tax_ids, 1)..array_upper(v_item_tax_ids, 1) LOOP
				IF v_item_tax_ids[t] = ANY(v_tax_id) THEN
					v_ind := system.array_index(v_tax_id, v_item_tax_ids[t]);
					v_tax_amount[v_ind] := v_tax_amount[v_ind] + CAST(v_item_tax_amounts[t] AS NUMERIC);
				ELSE
					v_tax_id := array_append(v_tax_id, v_item_tax_ids[t]);
					v_tax_amount := array_append(v_tax_amount, CAST(v_item_tax_amounts[t] AS NUMERIC));
				END IF;
			END LOOP;
		END LOOP;
		FOR i IN array_lower(v_tax_id, 1)..array_upper(v_tax_id, 1) LOOP
			SELECT INTO v_rec coa.id AS account_id, CASE WHEN position(shortname IN coa.name) > 0 THEN 'Expense report #' || er.id ELSE shortname || ' on expense report #' || er.id END AS description FROM ledger.general_chart_of_accounts coa, system.transaction_tax tt, system.linked_account la, system.expense_report er  WHERE coa.id = account_id AND tt.shortname = transaction_tax_shortname AND tt.id = v_tax_id[i] AND er.id = v_expense_report_id AND transaction_type = 'retail purchase';
			v_gl_descriptions := v_gl_descriptions || CAST(v_rec.description AS TEXT);
			v_gl_account_ids := v_gl_account_ids || CAST(v_rec.account_id AS TEXT);
			v_gl_amounts := v_gl_amounts || CAST(v_tax_amount[i] AS TEXT);
			v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
		END LOOP;
	END IF;

	-- prepare journal... line items (ex tax)
	FOR i IN array_lower(p_item_description, 1)..array_upper(p_item_description, 1) LOOP
		v_item_total_tax := 0;
		IF p_item_tax_ids IS NOT NULL THEN
			v_item_tax_ids := string_to_array(p_item_tax_ids[i], ',');
			v_item_tax_amounts := string_to_array(p_item_tax_amounts[i], ',');
			FOR t IN array_lower(v_item_tax_amounts, 1)..array_upper(v_item_tax_amounts, 1) LOOP
				v_item_total_tax := v_item_total_tax + CAST(v_item_tax_amounts[t] AS NUMERIC);
			END LOOP;
		END IF;
		v_gl_descriptions := v_gl_descriptions || p_item_description[i];
		v_gl_account_ids := v_gl_account_ids || p_item_account_id[i];
		v_gl_amounts := v_gl_amounts || CAST(CAST(p_item_amount[i] AS NUMERIC) - v_item_total_tax AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
	END LOOP;

	SELECT INTO v_error create_journal(v_gl_descriptions, v_gl_account_ids, v_gl_amounts, v_gl_drcrs, NULL, NULL, NULL, NULL, TRUE);
	IF v_error % 100 != 0 THEN
		RAISE EXCEPTION 'called function returned error';
	END IF;
	SELECT INTO v_journal_id currval('system.journal_id_seq');

	UPDATE system.expense_report SET journal_id = v_journal_id WHERE id = v_expense_report_id;

	-- We are assuming the description/account_id combination is unique with the expense
	-- report. This should be validated - probably both here and in the js.

	-- store tax records for each item to facilitate regeneration of the expense report
	IF p_item_tax_ids IS NOT NULL THEN
		FOR i IN array_lower(p_item_account_id, 1)..array_upper(p_item_account_id, 1) LOOP
			SELECT INTO v_journal_item_id id FROM system.general_journal_item WHERE journal_id = v_journal_id AND description = p_item_description[i] AND account_id = p_item_account_id[i];
			v_item_tax_ids := string_to_array(p_item_tax_ids[i], ',');
			v_item_tax_amounts := string_to_array(p_item_tax_amounts[i], ',');
			FOR t IN array_lower(v_item_tax_ids, 1)..array_upper(v_item_tax_ids, 1) LOOP
				INSERT INTO system.expense_report_item_tax (journal_item_id, transaction_tax_id, amount) VALUES (v_journal_item_id, CAST(v_item_tax_ids[t] AS INT), CAST(v_item_tax_amounts[t] AS NUMERIC));
			END LOOP;
		END LOOP;
	END IF;

	RETURN 31000;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 31099;
END;
$$ LANGUAGE plpgsql;
		
CREATE OR REPLACE FUNCTION generate_receipt (
	p_receipt_id TEXT,
	p_customer_id TEXT,
	p_amount TEXT,
	p_comment TEXT,
	p_invoice_ids TEXT[]) RETURNS INT AS $$
DECLARE
	v_receipt_id BIGINT;
	v_special_ac_cash TEXT;
	v_ar_account TEXT;
	v_customer_ac INT;
	v_journal_id BIGINT;
	v_journal_item_id BIGINT;
	v_gl_descriptions TEXT[];
	v_gl_account_ids TEXT[];
	v_gl_amounts TEXT[];
	v_gl_drcrs TEXT[];
	v_sl_descriptions TEXT[];
	v_sl_account_ids TEXT[];
	v_sl_amounts TEXT[];
	v_sl_drcrs TEXT[];
	v_error INT;
BEGIN
	-- create the receipt and get the id
	IF p_receipt_id IS NULL THEN
		SELECT INTO v_receipt_id nextval('system.receipt_id_seq');
	ELSE
		v_receipt_id = p_receipt_id;
	END IF;
	INSERT INTO system.receipt VALUES (v_receipt_id, 'NOW', CAST(p_invoice_ids AS BIGINT[]), NULL);

	-- prepare journal... Cash account
	SELECT INTO v_special_ac_cash code_value FROM system.sys_code WHERE code_id = 'special_ac_cash';
	v_gl_descriptions[1] := 'Receipt #' || v_receipt_id || COALESCE(' - ' || p_comment, '');
	v_gl_account_ids[1] := v_special_ac_cash;
	v_gl_amounts[1] := p_amount;
	v_gl_drcrs[1] := CAST('DR' AS TEXT);

	-- prepare journal... AR control account
	SELECT INTO v_ar_account id FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname = 'ar';
	v_gl_descriptions := v_gl_descriptions || CAST('Receipt #' || v_receipt_id || COALESCE(' - ' || p_comment, '') AS TEXT);
	v_gl_account_ids := v_gl_account_ids || v_ar_account;
	v_gl_amounts := v_gl_amounts || CAST(p_amount AS TEXT);
	v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);

	-- prepare journal... AR ledger
	SELECT INTO v_customer_ac id FROM ledger.ar_chart_of_accounts WHERE name = '#customer:' || p_customer_id;
	v_sl_descriptions[1] := 'Receipt #' || v_receipt_id || COALESCE(' - ' || p_comment, '');
	v_sl_account_ids[1] := CAST(v_customer_ac AS TEXT);
	v_sl_amounts[1] := p_amount;
	v_sl_drcrs[1] := CAST('CR' AS TEXT);

	-- create the journal and stamp its id onto the receipt
	SELECT INTO v_error create_ar_journal(v_gl_descriptions, v_gl_account_ids, v_gl_amounts, v_gl_drcrs, v_sl_descriptions, v_sl_account_ids, v_sl_amounts, v_sl_drcrs, TRUE);
	IF v_error % 100 != 0 THEN
		RAISE EXCEPTION 'called function returned error';
	END IF;
	SELECT INTO v_journal_id currval('system.journal_id_seq');
	UPDATE system.receipt SET journal_id = v_journal_id WHERE id = v_receipt_id;

	RETURN 31100;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 31199;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.po_item_tax (
	p_purchase_order_item_id BIGINT,
	p_tax_id INT) RETURNS NUMERIC AS $$
DECLARE
	v_tax RECORD;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_tax * FROM system.transaction_tax WHERE id = p_tax_id;
	SELECT INTO v_result (qty * unit_price - system.po_item_included_tax(p_purchase_order_item_id)) * COALESCE(v_tax.rate,0) / 100 FROM system.purchase_order_item WHERE id = p_purchase_order_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.po_item_included_tax (p_purchase_order_item_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_included_tax_rate NUMERIC;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_included_tax_rate COALESCE(sum(rate), 0) FROM system.transaction_tax WHERE id IN (SELECT transaction_tax_id FROM system.purchase_item_tax WHERE purchase_order_item_id = p_purchase_order_item_id AND included_in_unit_price);
	SELECT INTO v_result qty * unit_price * v_included_tax_rate / (100 + v_included_tax_rate) FROM system.purchase_order_item WHERE id = p_purchase_order_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.po_item_combined_tax (p_purchase_order_item_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_tax_rate NUMERIC;
	v_result NUMERIC;
BEGIN
	SELECT INTO v_tax_rate COALESCE(sum(rate),0) FROM system.transaction_tax WHERE id IN (SELECT transaction_tax_id FROM system.purchase_item_tax WHERE purchase_order_item_id = p_purchase_order_item_id);
	SELECT INTO v_result (qty * unit_price - system.po_item_included_tax(p_purchase_order_item_id)) * v_tax_rate / 100 FROM system.purchase_order_item WHERE id = p_purchase_order_item_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE VIEW system.purchase_order_tax_aggregation AS SELECT purchase_order_id, tt.shortname, round(sum(system.po_item_tax(pi.id, tt.id)), 2) AS individual_tax_total FROM system.purchase_order_item pi, system.purchase_item_tax pt, system.transaction_tax tt WHERE pt.purchase_order_item_id = pi.id AND pt.transaction_tax_id = tt.id GROUP BY purchase_order_id, shortname;

CREATE OR REPLACE FUNCTION system.purchase_order_total_ex_tax (p_purchase_order_id BIGINT) RETURNS NUMERIC as $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result round(sum(item_total_ex_tax), 2) FROM (SELECT purchase_order_id, qty * unit_price - COALESCE(system.po_item_included_tax(id),round(0,2)) AS item_total_ex_tax FROM system.purchase_order_item WHERE purchase_order_id = p_purchase_order_id) AS totals WHERE purchase_order_id = p_purchase_order_id GROUP BY purchase_order_id;
	RETURN v_result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.purchase_order_tax (p_purchase_order_id BIGINT) RETURNS NUMERIC AS $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result COALESCE(sum(individual_tax_total),round(0,2)) FROM system.purchase_order_tax_aggregation WHERE purchase_order_id = p_purchase_order_id;
	RETURN v_result;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION system.purchase_order_total (p_purchase_order_id BIGINT) RETURNS NUMERIC as $$
DECLARE
	v_result NUMERIC;
BEGIN
	SELECT INTO v_result system.purchase_order_total_ex_tax(p_purchase_order_id) + system.purchase_order_tax(p_purchase_order_id);
	RETURN v_result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_purchase_order (
	p_supplier_id TEXT,
	p_purchase_order_id TEXT,
	p_delivery_location_id TEXT,
	p_expense_account_id TEXT,
	p_item_qty TEXT[],
	p_item_description TEXT[],
	p_item_unit_price TEXT[],
	p_item_tax_ids TEXT[],
	p_item_tax_inclusions TEXT[]) RETURNS INT AS $$
DECLARE
	v_purchase_order_id BIGINT;
	v_journal_id BIGINT;
	v_purchase_order_item_id BIGINT;
	v_item_tax_ids TEXT[];
	v_item_tax_inclusions TEXT[];
	v_error INT;
BEGIN
	-- create the purchase order and get the id
	IF p_supplier_id IS NULL OR p_delivery_location_id IS NULL OR array_upper(p_item_description, 1) IS NULL THEN
		RAISE EXCEPTION 'Not enough parameters';
	END IF;
	IF p_purchase_order_id IS NULL THEN
		SELECT INTO v_purchase_order_id nextval('system.purchase_order_id_seq');
	ELSE
		v_purchase_order_id = p_purchase_order_id;
	END IF;
	INSERT INTO system.purchase_order VALUES (v_purchase_order_id, 'NOW', CAST(p_supplier_id AS INT), p_expense_account_id,CAST(p_delivery_location_id AS INT));

	-- We are assuming that all the item arrays are the same length. Ultimately,
	-- it would be good to verify this fact.

	-- create the purchase order items (and tax records for each item - these are in a
	-- separate table)
	FOR i IN array_lower(p_item_qty, 1)..array_upper(p_item_qty, 1) LOOP
		SELECT INTO v_purchase_order_item_id nextval('system.purchase_order_item_id_seq');
		INSERT INTO system.purchase_order_item (id, purchase_order_id, qty, description, unit_price) VALUES (v_purchase_order_item_id , v_purchase_order_id , CAST(p_item_qty[i] AS INT) , p_item_description[i] , CAST(p_item_unit_price[i] AS NUMERIC));
		v_item_tax_ids := string_to_array(p_item_tax_ids[i], ',');
		v_item_tax_inclusions := string_to_array(p_item_tax_inclusions[i], ',');
		IF v_item_tax_ids IS NOT NULL THEN
			FOR t in array_lower(v_item_tax_ids, 1)..array_upper(v_item_tax_ids, 1) LOOP
				EXECUTE 'INSERT INTO system.purchase_item_tax (purchase_order_item_id, transaction_tax_id, included_in_unit_price) VALUES (' || v_purchase_order_item_id || ', ' || v_item_tax_ids[t] || ', ''' || v_item_tax_inclusions[t] || ''')';
			END LOOP;
		END IF;
	END LOOP;
	RETURN 20200;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 20299;
END;
$$ LANGUAGE plpgsql;

CREATE VIEW system.purchase_order_item_info AS SELECT purchase_order_id, description, round(qty * unit_price - COALESCE(system.po_item_included_tax(pi.id),0), 2) AS total_ex, round(COALESCE(system.po_item_combined_tax(pi.id),0), 2) as combined_tax, round(qty * unit_price - COALESCE(system.po_item_included_tax(pi.id),0) + COALESCE(system.po_item_combined_tax(pi.id),0), 2) as total_inc FROM system.purchase_order_item pi ORDER BY id;

CREATE OR REPLACE FUNCTION pay_bill (
	p_supplier_id TEXT,
	p_amount TEXT,
	p_tax_id TEXT[],
	p_tax_amount TEXT[],
	p_comment TEXT,
	p_adjustment_expense_account TEXT,
	p_adjustment_comment TEXT,
	p_purchase_order_ids TEXT[]) RETURNS INT AS $$
DECLARE
	v_bill_payment_id BIGINT;
	v_special_ac_cash TEXT;
	v_ap_account TEXT;
	v_journal_id BIGINT;
	v_journal_item_id BIGINT;
	v_supplier_ac INT;
	v_rec RECORD;
	v_purchase_order_amount NUMERIC;
	v_total_purchase_orders NUMERIC:=0;
	v_total_purchase_orders_ex_tax NUMERIC:=0;
	v_bill_total_ex_tax NUMERIC;
	v_gl_descriptions TEXT[];
	v_gl_account_ids TEXT[];
	v_gl_amounts TEXT[];
	v_gl_drcrs TEXT[];
	v_sl_descriptions TEXT[];
	v_sl_account_ids TEXT[];
	v_sl_amounts TEXT[];
	v_sl_drcrs TEXT[];
	v_error INT;
BEGIN
	-- create the bill payment and get the id
	SELECT INTO v_bill_payment_id nextval('system.bill_payment_id_seq');
	INSERT INTO system.bill_payment VALUES (v_bill_payment_id, 'NOW', CAST(p_purchase_order_ids AS BIGINT[]), NULL);

	-- prepare journal... Cash account
	SELECT INTO v_special_ac_cash code_value FROM system.sys_code WHERE code_id = 'special_ac_cash';
	v_gl_descriptions[1] := 'Bill payment #' || v_bill_payment_id || COALESCE(' - ' || p_comment, '');
	v_gl_account_ids[1] := v_special_ac_cash;
	v_gl_amounts[1] := CAST(p_amount AS TEXT);
	v_gl_drcrs[1] := 'CR';

	-- prepare journal... AP control account
	SELECT INTO v_ap_account id FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname = 'ap';
	FOR i IN array_lower(p_purchase_order_ids, 1)..array_upper(p_purchase_order_ids, 1) LOOP
		v_purchase_order_amount := system.purchase_order_total(CAST(p_purchase_order_ids[i] AS BIGINT));
		v_gl_descriptions := v_gl_descriptions || CAST('Purchase order #' || p_purchase_order_ids[i] AS TEXT);
		v_gl_account_ids := v_gl_account_ids || v_ap_account;
		v_gl_amounts := v_gl_amounts || CAST(v_purchase_order_amount AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);
		v_total_purchase_orders := v_total_purchase_orders + v_purchase_order_amount;
	END LOOP;
	-- adjustment
	IF CAST(p_amount AS NUMERIC) != v_total_purchase_orders THEN
		v_gl_descriptions := v_gl_descriptions || CAST('Bill payment #' || v_bill_payment_id || COALESCE(' - adjustment for ' || p_adjustment_comment, '') AS TEXT);
		v_gl_account_ids := v_gl_account_ids || v_ap_account;
		v_gl_amounts := v_gl_amounts || CAST(ABS(CAST(p_amount AS NUMERIC) - v_total_purchase_orders) AS TEXT);
		IF CAST(p_amount AS NUMERIC) > v_total_purchase_orders THEN
			v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);
		ELSE
			v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
		END IF;
	END IF;
	-- payment
	v_gl_descriptions := v_gl_descriptions || CAST('Payment on account - Bill #' || v_bill_payment_id AS TEXT);
	v_gl_account_ids := v_gl_account_ids || v_ap_account;
	v_gl_amounts := v_gl_amounts || CAST(p_amount AS TEXT);
	v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);

	-- prepare journal... transaction taxes
	v_bill_total_ex_tax := p_amount;
	FOR i IN array_lower(p_tax_id, 1)..array_upper(p_tax_id, 1) LOOP
		SELECT INTO v_rec CASE WHEN position(shortname in coa.name) > 0 THEN 'Bill payment #' || v_bill_payment_id ELSE shortname || ' on bill payment #' || v_bill_payment_id END AS description, account_id FROM system.linked_account la, ledger.general_chart_of_accounts coa, system.transaction_tax tt WHERE la.account_id = coa.id AND la.transaction_tax_shortname = tt.shortname AND lower(la.transaction_type) = 'retail purchase' AND tt.id = CAST(p_tax_id[i] AS INT);
		IF CAST(p_tax_amount[i] AS NUMERIC) > 0 THEN
			v_gl_descriptions := v_gl_descriptions || v_rec.description;
			v_gl_account_ids := v_gl_account_ids || CAST(v_rec.account_id AS TEXT);
			v_gl_amounts := v_gl_amounts || CAST(p_tax_amount[i] AS TEXT);
			v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
			v_bill_total_ex_tax := v_bill_total_ex_tax - CAST(p_tax_amount[i] AS NUMERIC);
		END IF;
	END LOOP;

	-- prepare journal... book expenses
	FOR i IN array_lower(p_purchase_order_ids, 1)..array_upper(p_purchase_order_ids, 1) LOOP
		SELECT INTO v_rec system.purchase_order_total_ex_tax(CAST(p_purchase_order_ids[i] AS BIGINT)), expense_account_id FROM system.purchase_order WHERE id = CAST(p_purchase_order_ids[i] AS BIGINT);
		v_gl_descriptions := v_gl_descriptions || CAST('Purchase order #' || p_purchase_order_ids[i] AS TEXT);
		v_gl_account_ids := v_gl_account_ids || CAST(v_rec.expense_account_id AS TEXT);
		v_gl_amounts := v_gl_amounts || CAST(v_rec.purchase_order_total_ex_tax AS TEXT);
		v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
		v_total_purchase_orders_ex_tax := v_total_purchase_orders_ex_tax + v_rec.purchase_order_total_ex_tax;
	END LOOP;
	-- adjustment
	IF v_bill_total_ex_tax != v_total_purchase_orders_ex_tax THEN
		v_gl_descriptions := v_gl_descriptions || CAST('Bill payment #' || v_bill_payment_id || COALESCE(' - ' || p_adjustment_comment, '') AS TEXT);
		v_gl_account_ids := v_gl_account_ids || p_adjustment_expense_account;
		v_gl_amounts := v_gl_amounts || CAST(ABS(v_bill_total_ex_tax - v_total_purchase_orders_ex_tax) AS TEXT);
		IF v_bill_total_ex_tax > v_total_purchase_orders_ex_tax THEN
			v_gl_drcrs := v_gl_drcrs || CAST('DR' AS TEXT);
		ELSE
			v_gl_drcrs := v_gl_drcrs || CAST('CR' AS TEXT);
		END IF;
	END IF;

	-- prepare journal... AP ledger
	SELECT INTO v_supplier_ac id FROM ledger.ap_chart_of_accounts WHERE name = '#supplier:' || p_supplier_id;
	FOR i in array_lower(p_purchase_order_ids, 1)..array_upper(p_purchase_order_ids, 1) LOOP
		SELECT INTO v_rec system.purchase_order_total(CAST(p_purchase_order_ids[i] AS BIGINT));
		IF i = 1 THEN
			v_sl_descriptions[1] := 'Purchase order #' || p_purchase_order_ids[i];
			v_sl_account_ids[1] := CAST(v_supplier_ac AS TEXT);
			v_sl_amounts[1] := CAST(v_rec.purchase_order_total AS TEXT);
			v_sl_drcrs[1] := 'CR';
		ELSE
			v_sl_descriptions := v_sl_descriptions || CAST('Purchase order #' || p_purchase_order_ids[i] AS TEXT);
			v_sl_account_ids := v_sl_account_ids || CAST(v_supplier_ac AS TEXT);
			v_sl_amounts := v_sl_amounts || CAST(v_rec.purchase_order_total AS TEXT);
			v_sl_drcrs := v_sl_drcrs || CAST('CR' AS TEXT);
		END IF;
	END LOOP;
	-- adjustment
	IF CAST(p_amount AS NUMERIC) != v_total_purchase_orders THEN
		v_sl_descriptions := v_sl_descriptions || CAST('Bill payment #' || v_bill_payment_id || COALESCE(' - adjustment for ' || p_adjustment_comment, '') AS TEXT);
		v_sl_account_ids := v_sl_account_ids || CAST(v_supplier_ac AS TEXT);
		v_sl_amounts := v_sl_amounts || CAST(ABS(CAST(p_amount AS NUMERIC) - v_total_purchase_orders) AS TEXT);
		IF CAST(p_amount AS NUMERIC) > v_total_purchase_orders THEN
			v_sl_drcrs := v_sl_drcrs || CAST('CR' AS TEXT);
		ELSE
			v_sl_drcrs := v_sl_drcrs || CAST('DR' AS TEXT);
		END IF;
	END IF;
	-- payment
	v_sl_descriptions := v_sl_descriptions || CAST('Payment on account - Bill #' || v_bill_payment_id AS TEXT);
	v_sl_account_ids := v_sl_account_ids || CAST(v_supplier_ac AS TEXT);
	v_sl_amounts := v_sl_amounts || CAST(p_amount AS TEXT);
	v_sl_drcrs := v_sl_drcrs || CAST('DR' AS TEXT);

	-- create the journal and stamp its id onto the bill payment
	SELECT INTO v_error create_ap_journal(v_gl_descriptions, v_gl_account_ids, v_gl_amounts, v_gl_drcrs, v_sl_descriptions, v_sl_account_ids, v_sl_amounts, v_sl_drcrs, TRUE);
	IF v_error % 100 != 0 THEN
		RAISE EXCEPTION 'called function returned error';
	END IF;
	SELECT INTO v_journal_id currval('system.journal_id_seq');
	UPDATE system.bill_payment SET journal_id = v_journal_id WHERE id = v_bill_payment_id;

	RETURN 20300;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 20399;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_contact(
	p_surname VARCHAR(30),
	p_given_name VARCHAR(30),
	p_company_name VARCHAR(30),
	p_position VARCHAR(30),
	p_line1 VARCHAR(50),
	p_line2 VARCHAR(50),
	p_suburb VARCHAR(20),
	p_region VARCHAR(20),
	p_code VARCHAR(10),
	p_country VARCHAR(20),
	p_location_id INT,
	p_loc_phone VARCHAR(20),
	p_loc_fax VARCHAR(20),
	p_phone VARCHAR(100),
	p_fax VARCHAR(100),
	p_email VARCHAR(100),
	p_mobile VARCHAR(100),
	p_sms VARCHAR(100),
	p_company_id INT,
	p_location_name VARCHAR(10),
	p_entity_type VARCHAR(30)) RETURNS INT AS $$
DECLARE
	v_person_id INT :=null;
	v_company_id INT :=null;
	v_address_id INT :=null;
	v_location_id INT :=null;
	v_phone VARCHAR(20) :=null;
	v_fax VARCHAR(20) :=null;
	v_location_name VARCHAR(20) :=null;
	v_relation_type VARCHAR(10) :='employee';
	v_type VARCHAR(30) :=null;
	v_company_name VARCHAR(30) :=null;
	v_entity_id INT :=null;
	v_relationship_id INT :=null;
	v_phone_id INT :=NULL;
	v_fax_id INT :=NULL;
	v_email_id INT :=NULL;
	v_mobile_id INT :=NULL;
	v_sms_id INT :=NULL;
	v_position_id INT :=NULL;
	v_flag INT := 0;
BEGIN
---create person and get the person''s id
	IF p_surname IS NOT NULL AND p_surname<>'' AND p_given_name IS NOT NULL AND p_given_name<>'' THEN
		v_type :='person';
		INSERT INTO system.entity(id,type,name,given_name) VALUES(DEFAULT,v_type,p_surname,p_given_name);
		SELECT CURRVAL('system.entity_id_seq') INTO v_person_id;
	ELSE
		RAISE EXCEPTION 'Can not create person,error occurs';
	END IF;
---get the company''s id(direct or create) and create the address and the location
	IF p_location_id IS NOT NULL THEN
		v_location_id := p_location_id;
		SELECT entity_id INTO v_company_id FROM system.location WHERE id = v_location_id;
	ELSIF p_company_id IS NOT NULL THEN
		v_company_id := p_company_id;
		IF p_location_id IS NOT NULL THEN 
			v_location_id := p_location_id;
		ELSIF p_location_name IS NOT NULL AND p_location_name<>'' AND p_line1 IS NOT NULL AND p_line1<>'' AND p_suburb IS NOT NULL AND p_suburb<>'' AND p_region IS NOT NULL AND p_region<>'' AND p_code IS NOT NULL AND p_code<>'' AND p_country IS NOT NULL AND p_country<>'' THEN
			INSERT INTO system.address(line1,line2,suburb,region,code,country) VALUES(p_line1,p_line2,p_suburb,p_region,p_code,p_country);
			SELECT CURRVAL('system.address_id_seq') INTO v_address_id;
			v_location_name := p_location_name;
			v_phone := p_loc_phone;
			v_fax := p_loc_fax;
			INSERT INTO system.location(entity_id, address_id, name,phone,fax) VALUES (v_company_id,v_address_id,v_location_name,v_phone,v_fax);
			SELECT CURRVAL('system.location_id_seq') INTO v_location_id;
		ELSE						
			v_flag := 2;
		END IF;			
	ELSIF p_company_name IS NOT NULL AND p_company_name<>'' THEN
		v_type :=p_entity_type;
		v_company_name :=p_company_name;
		INSERT INTO system.entity(type,name) VALUES(v_type,v_company_name);
		SELECT CURRVAL('system.entity_id_seq') INTO v_company_id;
		IF p_location_name IS NOT NULL AND p_location_name<>'' AND p_line1 IS NOT NULL AND p_line1<>'' AND p_suburb IS NOT NULL AND p_suburb<>'' AND p_region IS NOT NULL AND p_region<>'' AND p_code IS NOT NULL AND p_code<>'' AND p_country IS NOT NULL AND p_country<>'' THEN
			INSERT INTO system.address(line1,line2,suburb,region,code,country) VALUES(p_line1,p_line2,p_suburb,p_region,p_code,p_country);
			SELECT CURRVAL('system.address_id_seq') INTO v_address_id;
			v_location_name := p_location_name;
			v_phone := p_loc_phone;
			v_fax := p_loc_fax;
			INSERT INTO system.location(entity_id, address_id, name,phone,fax) VALUES (v_company_id,v_address_id,v_location_name,v_phone,v_fax);
			SELECT CURRVAL('system.location_id_seq') INTO v_location_id;
		ELSE						
			v_flag := 2;
		END IF;			
	ELSIF p_line1 IS NOT NULL AND p_line1<>'' AND p_suburb IS NOT NULL AND p_suburb<>'' AND p_region IS NOT NULL AND p_region<>'' AND p_code IS NOT NULL AND p_code<>'' AND p_country IS NOT NULL AND p_country<>'' THEN
		INSERT INTO system.address(line1,line2,suburb,region,code,country) VALUES(p_line1,p_line2,p_suburb,p_region,p_code,p_country);
		SELECT CURRVAL('system.address_id_seq') INTO v_address_id;					
		v_location_name := 'work';		   
		INSERT INTO system.location(entity_id, address_id, name) VALUES (v_person_id,v_address_id,v_location_name);
		SELECT CURRVAL('system.location_id_seq') INTO v_location_id;
	ELSE					
		v_flag := 1;
	END IF;
	--create relationship(the v_company_id and the v_person_id both exists)
	IF v_flag<>1 AND v_company_id IS NOT NULL AND v_person_id IS NOT NULL THEN
		IF v_flag=0 THEN
			INSERT INTO system.relationship(entity_id,rel_entity_id,type,location_id) VALUES(v_person_id,v_company_id,v_relation_type,v_location_id);
		ELSIF v_flag=2 THEN
			INSERT INTO system.relationship(entity_id,rel_entity_id,type) VALUES(v_person_id,v_company_id,v_relation_type);
		END IF;
		SELECT CURRVAL('system.relationship_id_seq') INTO v_relationship_id;
	END IF;
	--create contanct detail		
	IF p_phone IS NOT NULL AND p_phone<>'' THEN
		IF p_loc_phone IS NOT NULL AND p_loc_phone<>'' THEN 
			IF p_phone <> p_loc_phone THEN
				INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'phone',p_phone);
				SELECT CURRVAL('system.contact_detail_id_seq') INTO v_phone_id;
			END IF;
		ELSE				
			INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'phone',p_phone);	  
			SELECT CURRVAL('system.contact_detail_id_seq') INTO v_phone_id;
		END IF;
	ELSE
		RAISE NOTICE 'Can not create phone,doing nothing';
	END IF;
	IF p_fax IS NOT NULL AND p_fax<>''THEN
		IF p_loc_fax IS NOT NULL AND p_loc_fax<>'' THEN 
			IF p_fax <> p_loc_fax THEN
				INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'fax',p_fax);
				SELECT CURRVAL('system.contact_detail_id_seq') INTO v_fax_id;
			END IF;
		ELSE				
			INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'fax',p_fax);		
				SELECT CURRVAL('system.contact_detail_id_seq') INTO v_fax_id;
		END IF;
	ELSE
		RAISE NOTICE 'Can not create fax,doing nothing';
	END IF;   
	IF p_email IS NOT NULL AND p_email<>'' THEN
		INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'email', p_email);
		SELECT CURRVAL('system.contact_detail_id_seq') INTO v_email_id;
	ELSE
		RAISE NOTICE 'Can not create email,doing nothing';
	END IF;
	IF p_mobile IS NOT NULL AND p_mobile<>''THEN
		INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'mobile',p_mobile);		
		SELECT CURRVAL('system.contact_detail_id_seq') INTO v_mobile_id;
	ELSE
		RAISE NOTICE 'Can not create mobile,doing nothing';
	END IF;
	IF p_sms IS NOT NULL AND p_sms<>'' THEN
		INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'sms',p_sms);
		SELECT CURRVAL('system.contact_detail_id_seq') INTO v_sms_id;
	ELSE
		RAISE NOTICE 'Can not create sms,doing nothing';
	END IF;
	IF p_position IS NOT NULL AND p_position<>'' THEN
		INSERT INTO system.contact_detail(entity_id,type,detail) VALUES(v_person_id,'position',p_position);
		SELECT CURRVAL('system.contact_detail_id_seq') INTO v_position_id;
	ELSE
		RAISE NOTICE 'Can not create position,doing nothing';
	END IF;
	RETURN 50100;
	EXCEPTION
		WHEN RAISE_EXCEPTION THEN
			RETURN 50101;
		WHEN OTHERS THEN
			RETURN 50199;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_contact(person_id integer)
 RETURNS integer AS
$$
DECLARE
	v_iscustomer boolean := false;
	v_issupplier boolean := false;
	v_success    integer := 50300;
	v_error	     integer := 50399;
BEGIN
	select into v_iscustomer true from customer where entity_id = person_id;
	select into v_issupplier true from supplier where entity_id = person_id;
	
	if v_iscustomer or v_issupplier then
		update system.entity set status = 'DELETED' where id = person_id;
	else
		delete from system.contact_detail where entity_id = person_id;
        delete from system.location where entity_id = person_id;
		delete from system.relationship where entity_id = person_id;
		delete from system.entity where id = person_id;
	end if;
	return v_success;
EXCEPTION
	WHEN query_canceled THEN
		return v_error;
	WHEN others THEN
		return v_error;
END;
$$
 LANGUAGE 'plpgsql';
 
 CREATE OR REPLACE FUNCTION merge_locations(p_person1_id integer, p_person2_id integer, p_location1 text[], p_location2 text[])
  RETURNS INTEGER AS
$$
DECLARE
	v_location1 TEXT;
	v_location2 TEXT;
	v_success INT := 50500;
	v_error   INT := 50599;
BEGIN
	IF p_location1 is not null then
		v_location1 := array_to_string(p_location1,',');
		EXECUTE 'delete from system.location where entity_id = '||p_person1_id||' and id not in ('||v_location1||')';
	ELSE
		EXECUTE 'delete from system.location where entity_id = '||p_person1_id;
	END IF;
	IF p_location2 is not null then
		v_location2 := array_to_string(p_location2,',');
		EXECUTE 'insert into system.location(entity_id,name,phone,fax,address_id,deactivated) select '||p_person1_id||',name,phone,fax,address_id,deactivated from system.location where id in ('||v_location2||')';
	END IF;
	RETURN v_success;
EXCEPTION
	WHEN query_canceled THEN
		RETURN v_error;
	WHEN others THEN
		RETURN v_error;
end
$$ 
LANGUAGE 'plpgsql';
 
CREATE OR REPLACE FUNCTION merge_contact(p_person1_id integer, p_person2_id integer, p_field_names text[], p_new_values text[], p_locations text[], p_original_page_load timestamp without time zone)
  RETURNS integer AS
$$
DECLARE
	v_person1 RECORD;
	v_person2 RECORD;
	v_field_names TEXT[];
	v_new_values TEXT[];
	v_relationship1 TEXT[];
	v_relationship2 TEXT[];
	v_location TEXT[];
	v_locations1 TEXT[];
	v_locations2 TEXT[];

	v_keep_person_id INT;
	v_merge_person_id INT;
	v_contact_status TEXT;
	v_edit_message INT;
	v_delete_message INT;
	v_flag INT := 1;
	v_returnum INT;
	v_success INT := 50400;
	v_error   INT := 50499;
BEGIN
	SELECT INTO v_person1 * FROM contact_info WHERE person_id = p_person1_id;
	SELECT INTO v_person2 * FROM contact_info WHERE person_id = p_person2_id;
	IF v_person1.iscustomer OR v_person1.issupplier THEN
		v_keep_person_id := p_person1_id;
		v_merge_person_id := p_person2_id;
		IF v_person2.iscustomer OR v_person2.issupplier THEN
			SELECT INTO v_edit_message edit_contact(p_person1_id,'','',p_field_names,p_new_values,p_original_page_load);
			v_contact_status := 'MERGED';
		ELSE
			SELECT INTO v_edit_message edit_contact(p_person1_id,'','',p_field_names,p_new_values,p_original_page_load);
			v_contact_status := 'DELETED';
		END IF;
	ELSE
		IF v_person2.iscustomer OR v_person2.issupplier THEN
			v_keep_person_id := p_person2_id;
			v_merge_person_id := p_person1_id;
			UPDATE system.entity SET given_name=v_person1.given_name, name = v_person1.surname
			WHERE id = p_person2_id;
			SELECT INTO v_edit_message edit_contact(p_person2_id,'','',p_field_names,p_new_values,p_original_page_load);
			v_contact_status := 'DELETED';
		ELSE
			v_keep_person_id := p_person1_id;
			v_merge_person_id := p_person2_id;
			SELECT INTO v_edit_message edit_contact(p_person1_id,'','',p_field_names,p_new_values,p_original_page_load);
			v_contact_status := 'DELETED';
		END IF;
	END IF;

	FOR i IN array_lower(p_locations,1)..array_upper(p_locations,1) LOOP
		v_location := string_to_array(p_locations[i],'_');
		IF v_location[1] = 'c' THEN
			IF v_location[3] = v_keep_person_id::TEXT THEN
				v_flag := v_flag + 2;
				v_relationship1 := v_location;
			ELSE
				v_flag := v_flag + 4;
				v_relationship2 := v_location; 
			END IF;
		ELSE
			IF v_location[3] = v_keep_person_id::TEXT THEN
				v_locations1 := array_append(v_locations1,v_location[4]);
			ELSE
				v_locations2 := array_append(v_locations2,v_location[4]); 
			END IF;
		END IF; 
	END LOOP;

	SELECT INTO v_returnum merge_locations(v_keep_person_id,v_merge_person_id,v_locations1,v_locations2);
	IF v_flag = 1 THEN
		IF v_person1.person_id = v_keep_person_id AND v_person1.company_id IS NOT NULL THEN
			DELETE FROM system.relationship WHERE entity_id = v_keep_person_id AND rel_entity_id = v_person1.company_id;
		ELSIF v_person2.person_id = v_keep_person_id AND v_person2.company_id IS NOT NULL THEN
			DELETE FROM system.relationship WHERE entity_id = v_keep_person_id AND rel_entity_id = v_person2.company_id;
		END IF;
	ELSIF v_flag = 5 THEN
		IF v_person1.person_id = v_keep_person_id AND v_person1.company_id IS NOT NULL THEN
			DELETE FROM system.relationship WHERE entity_id = v_keep_person_id AND rel_entity_id = v_person1.company_id;
		ELSIF v_person2.person_id = v_keep_person_id AND v_person2.company_id IS NOT NULL THEN
			DELETE FROM system.relationship WHERE entity_id = v_keep_person_id AND rel_entity_id = v_person2.company_id;
		END IF;
		EXECUTE 'INSERT INTO system.relationship(entity_id, rel_entity_id, type, location_id) SELECT '||v_keep_person_id||', rel_entity_id, type, location_id FROM system.relationship WHERE entity_id = '||v_merge_person_id||' AND rel_entity_id = '||v_relationship2[2];
	ELSIF v_flag = 7 THEN
		EXECUTE 'INSERT INTO system.location(entity_id,name,phone,fax,address_id,deactivated) SELECT '||v_keep_person_id||',name,phone,fax,address_id,deactivated FROM system.location WHERE id = '||v_relationship2[4];
	END IF;
	
		
	IF v_contact_status = 'MERGED' THEN
		UPDATE system.entity SET status = 'MERGED' WHERE id = v_merge_person_id;
	ELSIF v_contact_status = 'DELETED' THEN
		SELECT INTO v_returnum delete_contact(v_merge_person_id);
	END IF; 
	RETURN v_success;

END
$$
  LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION system.safe_update(
	p_table_name TEXT,
	p_where_cond TEXT,
	p_set_string TEXT,
	p_original_page_load TIMESTAMP) RETURNS INT AS $func$
DECLARE
	v_delimited_set_string TEXT;
	v_single_col_strings TEXT[];
	v_rec RECORD;
	v_has_recent_update BOOLEAN := FALSE;
	v_col_name TEXT;
	v_col_clash BOOLEAN;
	v_set_string TEXT := '';
	v_cols_updated TEXT := '';
	v_success_message INT := 70100;
BEGIN
	v_delimited_set_string := regexp_replace(p_set_string, '([^'',]*)?(''[^'']*'')?,', E'\\1\\2\\~:~', 'g'); --changes all commas not inside single quotes into ~:~
	v_single_col_strings := string_to_array(v_delimited_set_string, '~:~');
	FOR i IN array_lower(v_single_col_strings, 1)..array_upper(v_single_col_strings, 1) LOOP
		v_col_name := trim(split_part(v_single_col_strings[i], '=', 1));
		FOR v_rec IN EXECUTE 'SELECT * FROM system.row_update_log WHERE where_cond = '||quote_literal(p_where_cond)||' AND updated > '''||CAST(p_original_page_load AS TEXT)||'''' LOOP
			v_has_recent_update := TRUE;
			v_col_clash := FALSE;
			IF v_col_name = ANY (v_rec.cols_updated) THEN
				v_col_clash := TRUE;
			END IF;
		END LOOP;
		IF NOT v_col_clash THEN
			v_set_string = trim(', ' from v_set_string||', '||v_single_col_strings[i]);
			v_cols_updated := trim('~:~' from v_cols_updated||'~:~'||v_col_name);
		END IF;
	END LOOP;
	IF v_set_string = '' AND v_has_recent_update THEN
		RAISE EXCEPTION '70101';
	ELSIF v_set_string = '' THEN
		v_set_string := p_set_string;
		FOR i IN array_lower(v_single_col_strings, 1)..array_upper(v_single_col_strings, 1) LOOP
			v_col_name := trim(split_part(v_single_col_strings[i], '=', 1));
			v_cols_updated := trim('~:~' from v_cols_updated||'~:~'||v_col_name);
		END LOOP;
	ELSIF array_upper(v_single_col_strings, 1) != array_upper(string_to_array(v_cols_updated, '~:~'), 1) THEN
		v_success_message := 70150;
	END IF;
	EXECUTE $$UPDATE $$||p_table_name||$$ SET $$||v_set_string||$$ WHERE $$||p_where_cond;
	IF EXISTS (SELECT * FROM system.row_update_log WHERE table_name=p_table_name AND where_cond=p_where_cond) THEN
		UPDATE system.row_update_log SET updated='NOW' WHERE table_name=p_table_name AND where_cond=p_where_cond;
	ELSE
		INSERT INTO system.row_update_log VALUES (p_table_name, p_where_cond, string_to_array(v_cols_updated, '~:~'), 'NOW');
	END IF;
	RETURN v_success_message;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN CAST(SQLERRM AS INT);
	WHEN OTHERS THEN
		RETURN 70199;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.array_index (
	p_array TEXT[],
	p_search_string TEXT) RETURNS INT AS $$
DECLARE
	array_string TEXT;
	v_part_1 TEXT;
	v_result INT;
BEGIN
	array_string := array_to_string(p_array, '~delim~');
	IF position(p_search_string in array_string) = 0 THEN
		RETURN 0;
	END IF;
	v_part_1 := split_part(array_string, p_search_string, 1) || 'some terminating text';
	v_result := array_upper(string_to_array(v_part_1, '~delim~'), 1);
	RETURN v_result;
END;
$$ LANGUAGE plpgsql;
	
CREATE OR REPLACE FUNCTION edit_contact (
	p_person_id INT,
	p_surname TEXT,
	p_given_name TEXT,
	p_field_names TEXT[],
	p_new_values TEXT[],
	p_original_page_load TIMESTAMP) RETURNS INT AS $$
DECLARE
	v_company_id INT;
	v_address_id INT;
	v_line2 TEXT;
	v_location_type RECORD;
	v_location_phone TEXT;
	v_location_fax TEXT;
	v_location_id INT;
	v_old_location_id INT;
	v_relationship_id INT;
	v_detail_fields TEXT[];
	v_new_value TEXT;
	v_current_detail RECORD;
	v_error INT;
	v_success_message INT := 50200;
	v_type VARCHAR(30);
BEGIN
	-- update customer name if necessary
	IF p_surname IS NOT NULL AND p_surname<>'' AND p_given_name IS NOT NULL AND p_given_name <>'' THEN		
		SELECT INTO v_error system.safe_update('system.entity', 'id = ' || p_person_id , 'name = ''' || p_surname || ''',given_name='''|| p_given_name ||'''', p_original_page_load);
		IF v_error % 100 != 0 THEN
			IF v_error % 50 != 0 THEN
				RAISE EXCEPTION 'called function returned error';
			ELSE
				v_success_message := v_error;
			END IF;
		END IF;
	END IF;
	-- update contact details if necessary
	v_detail_fields = '{position,phone,fax,mobile,email,sms}';
	FOR i IN array_lower(v_detail_fields, 1)..array_upper(v_detail_fields, 1) LOOP
		IF v_detail_fields[i] = ANY (p_field_names) THEN
			v_new_value := p_new_values[system.array_index(p_field_names, v_detail_fields[i])];
			SELECT INTO v_current_detail detail FROM system.contact_detail WHERE entity_id = p_person_id AND type = v_detail_fields[i];
			IF NOT FOUND THEN
				INSERT INTO system.contact_detail VALUES (DEFAULT, p_person_id, v_detail_fields[i], v_new_value);
			ELSE
				SELECT INTO v_error system.safe_update('system.contact_detail', 'entity_id = ' || p_person_id || ' AND type = ''' || v_detail_fields[i] || '''', 'detail = ''' || TRIM(v_new_value) || '''', p_original_page_load);
				IF v_error % 100 != 0 THEN
					IF v_error % 50 != 0 THEN
						RAISE EXCEPTION 'called function returned error';
					ELSE
						v_success_message := v_error;
					END IF;
				END IF;
			END IF;
		END IF;
	END LOOP;

	-- update alt_location details (work, home, postal_address, etc) if necessary
	FOR v_location_type IN SELECT * FROM system.sys_code WHERE code_id = 'alt_location_type' LOOP
		IF v_location_type.code_value || '_line1' = ANY (p_field_names) THEN
			IF v_location_type.code_value || '_phone' = ANY (p_field_names) THEN
				v_location_phone := p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_phone')];
			ELSE
				v_location_phone := NULL;
			END IF;
			IF v_location_type.code_value || '_fax' = ANY (p_field_names) THEN
				v_location_fax := p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_fax')];
			ELSE
				v_location_fax := NULL;
			END IF;
			IF v_location_type.code_value || '_line2' = ANY (p_field_names) THEN
				v_line2 := p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_line2')];
			ELSE
				v_line2 := NULL;
			END IF;
			SELECT INTO v_address_id nextval('system.address_id_seq');
			INSERT INTO system.address VALUES (v_address_id, p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_line1')], v_line2, p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_suburb')], p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_region')], p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_code')], p_new_values[system.array_index(p_field_names, v_location_type.code_value || '_country')]);
			SELECT INTO v_location_id nextval('system.location_id_seq');
			INSERT INTO system.location (id, entity_id, name, phone, fax, address_id) VALUES (v_location_id, p_person_id, v_location_type.code_value, v_location_phone, v_location_fax, v_address_id);

			--tidy up the old location details
			SELECT INTO v_old_location_id id FROM system.location WHERE entity_id = p_person_id AND name = v_location_type.code_value AND deactivated IS NULL AND id != v_location_id;
			SELECT INTO v_error system.safe_update('system.location', 'id = ' || v_old_location_id, 'deactivated = ''NOW''', p_original_page_load);
			IF v_error % 100 != 0 THEN
				RAISE EXCEPTION 'called function returned error';
			END IF;

			-- re-initialise v_location_id to avoid triggering a bogus relationship update
			v_location_id := NULL;
		END IF;
	END LOOP;

	-- add new alt_location details if necessary
	IF 'alt_location_type' = ANY (p_field_names) THEN
		IF 'alt_location_phone' = ANY (p_field_names) THEN
			v_location_phone := p_new_values[system.array_index(p_field_names, 'alt_location_phone')];
		ELSE
			v_location_phone := NULL;
		END IF;
		IF 'location_fax' = ANY (p_field_names) THEN
			v_location_fax := p_new_values[system.array_index(p_field_names, 'alt_location_fax')];
		ELSE
			v_location_fax := NULL;
		END IF;
		IF 'alt_location_line2' = ANY (p_field_names) THEN
			v_line2 := p_new_values[system.array_index(p_field_names, 'alt_location_line2')];
		ELSE
			v_line2 := NULL;
		END IF;
		SELECT INTO v_address_id nextval('system.address_id_seq');
		INSERT INTO system.address VALUES (v_address_id, p_new_values[system.array_index(p_field_names, 'alt_location_line1')], v_line2, p_new_values[system.array_index(p_field_names, 'alt_location_suburb')], p_new_values[system.array_index(p_field_names, 'alt_location_region')], p_new_values[system.array_index(p_field_names, 'alt_location_code')], p_new_values[system.array_index(p_field_names, 'alt_location_country')]);
		SELECT INTO v_location_id nextval('system.location_id_seq');
		INSERT INTO system.location (id, entity_id, name, phone, fax, address_id) VALUES (v_location_id, p_person_id, p_new_values[system.array_index(p_field_names, 'alt_location_type')], v_location_phone, v_location_fax, v_address_id);

		-- re-initialise v_location_id to avoid triggering a bogus relationship update
		v_location_id := NULL;
	END IF;

	-- add company if necessary
	v_type := 'company';
	IF 'entity_type' = ANY(p_field_names) THEN
		v_type := p_new_values[system.array_index(p_field_names, 'entity_type')];
	END IF;
	IF 'company_name' = ANY (p_field_names) THEN		
		SELECT INTO v_company_id nextval('system.entity_id_seq');
		INSERT INTO system.entity (id, type, name) VALUES (v_company_id, v_type, p_new_values[system.array_index(p_field_names, 'company_name')]);
	END IF;
	IF v_company_id IS NULL THEN
		IF 'company_id' = ANY (p_field_names) THEN
			v_company_id := p_new_values[system.array_index(p_field_names, 'company_id')];
		END IF;
	END IF;

	-- add company location and address if necessary
	IF 'location_name' = ANY (p_field_names) THEN
		IF 'location_phone' = ANY (p_field_names) THEN
			v_location_phone := p_new_values[system.array_index(p_field_names, 'location_phone')];
		END IF;
		IF 'location_fax' = ANY (p_field_names) THEN
			v_location_fax := p_new_values[system.array_index(p_field_names, 'location_fax')];
		END IF;
		IF 'line2' = ANY (p_field_names) THEN
			v_line2 := p_new_values[system.array_index(p_field_names, 'line2')];
		END IF;
		SELECT INTO v_address_id nextval('system.address_id_seq');
		INSERT INTO system.address VALUES (v_address_id, p_new_values[system.array_index(p_field_names, 'line1')], v_line2, p_new_values[system.array_index(p_field_names, 'suburb')], p_new_values[system.array_index(p_field_names, 'region')], p_new_values[system.array_index(p_field_names, 'code')], p_new_values[system.array_index(p_field_names, 'country')]);
		SELECT INTO v_location_id nextval('system.location_id_seq');
		INSERT INTO system.location (id, entity_id, name, phone, fax, address_id) VALUES (v_location_id, v_company_id, p_new_values[system.array_index(p_field_names, 'location_name')], v_location_phone, v_location_fax, v_address_id);
	END IF;

	-- update or add relationship if necessary
	IF v_location_id IS NULL AND 'location_id' = ANY (p_field_names) THEN
		v_location_id := p_new_values[system.array_index(p_field_names, 'location_id')];
	END IF;
	IF v_location_id IS NOT NULL THEN
		IF v_company_id IS NULL THEN
			SELECT INTO v_company_id entity_id FROM system.location WHERE id = v_location_id;
		END IF;

		--obsolete the work location if it exists
		SELECT INTO v_old_location_id id FROM system.location WHERE entity_id = p_person_id AND name = 'work' AND deactivated IS NULL;
		IF FOUND THEN
			SELECT INTO v_error system.safe_update('system.location', 'id = ' || v_old_location_id, 'deactivated = ''NOW''', p_original_page_load);
			IF v_error % 100 != 0 THEN
				RAISE EXCEPTION 'called function returned error';
			END IF;
		END IF;
	END IF;
	IF v_company_id IS NOT NULL THEN
		SELECT INTO v_relationship_id id FROM system.relationship WHERE entity_id = p_person_id AND type = 'employee';
		IF v_relationship_id IS NOT NULL THEN
			IF v_location_id IS NULL THEN
				SELECT INTO v_error system.safe_update('system.relationship', 'id = ' || v_relationship_id, 'rel_entity_id = ' || COALESCE(CAST(v_company_id AS TEXT), 'comp'), p_original_page_load);
			ELSE
				SELECT INTO v_error system.safe_update('system.relationship', 'id = ' || v_relationship_id, 'rel_entity_id = ' || COALESCE(CAST(v_company_id AS TEXT), 'comp') || ', location_id = ' || COALESCE(CAST(v_location_id AS TEXT), 'loc'), p_original_page_load);
			END IF;
			IF v_error % 100 != 0 THEN
				IF v_error % 50 != 0 THEN
					RAISE EXCEPTION 'called function returned error';
				ELSE
					v_success_message := v_error;
				END IF;
			END IF;
		ELSE
			SELECT INTO v_relationship_id nextval('system.relationship_id_seq');
			INSERT INTO system.relationship (id, entity_id, rel_entity_id, type, location_id) VALUES (v_relationship_id, p_person_id, v_company_id, 'employee', v_location_id);
		END IF;
	END IF;

	RETURN v_success_message;
EXCEPTION
	WHEN RAISE_EXCEPTION THEN
		RETURN v_error;
	WHEN OTHERS THEN
		RETURN 50299;
END;
$$ LANGUAGE plpgsql;
	RETURN 0;
END;
$init_db$ LANGUAGE plpgsql;""" }
